SQL Server 2008中SQL：Values新用途

SQL Server 2008中新增功能：可以使用单个Insert命令插入多行。

[ruby] view plain copy print ?

1. Create table Demo_Values
2. (PKID int not null identity(1,1) primary key
3. ,DName Nvarchar(20) null
4. ,DCode NVarchar(30) null
5. ,DDate datetime null
6. )
7. go
8. --this SQL is only for SQL Server 2008
9. Insert into Demo_Values
10. (DName,DCode,DDate)
11. values
12. ('DemoA','AAA',GETDATE()),
13. ('DemoB','BBB',GETDATE()),
14. ('DemoC','CCC',GETDATE()),
15. ('DemoD','DDD',GETDATE()),
16. ('DemoE','EEE',GETDATE())
17. --(5 row(s) affected)

Create table Demo_Values (PKID int not null identity(1,1) primary key ,DName Nvarchar(20) null ,DCode NVarchar(30) null ,DDate datetime null ) go --this SQL is only for SQL Server 2008 Insert into Demo_Values (DName,DCode,DDate) values ('DemoA','AAA',GETDATE()), ('DemoB','BBB',GETDATE()), ('DemoC','CCC',GETDATE()), ('DemoD','DDD',GETDATE()), ('DemoE','EEE',GETDATE()) --(5 row(s) affected)

除了可以一次性插入多行，values还有更巧妙的用法，不使用永久表或临时表而表示一个结果集，而且不需要函数或表变量。看下面的例子：　

[ruby] view plain copy print ?

1. --this SQL is only for SQL Server 2008
2. select DName,DCode,DDate
3. from
4. (values
5. ('DemoA','AAA',GETDATE()),
6. ('DemoB','BBB',GETDATE()),
7. ('DemoC','CCC',GETDATE()),
8. ('DemoD','DDD',GETDATE()),
9. ('DemoE','EEE',GETDATE())
10. )
11. Demo_Values (DName,DCode,DDate)
12. --(5 row(s) affected)
13. /*
14. DName DCode DDate
15. DemoA AAA 2010-10-12 20:37:45.500
16. DemoB BBB 2010-10-12 20:37:45.500
17. DemoC CCC 2010-10-12 20:37:45.500
18. DemoD DDD 2010-10-12 20:37:45.500
19. DemoE EEE 2010-10-12 20:37:45.500
20. */

--this SQL is only for SQL Server 2008 select DName,DCode,DDate from (values ('DemoA','AAA',GETDATE()), ('DemoB','BBB',GETDATE()), ('DemoC','CCC',GETDATE()), ('DemoD','DDD',GETDATE()), ('DemoE','EEE',GETDATE()) ) Demo_Values (DName,DCode,DDate) --(5 row(s) affected) /* DName DCode DDate DemoA AAA 2010-10-12 20:37:45.500 DemoB BBB 2010-10-12 20:37:45.500 DemoC CCC 2010-10-12 20:37:45.500 DemoD DDD 2010-10-12 20:37:45.500 DemoE EEE 2010-10-12 20:37:45.500 */

注意该语句的最后一行定义了“源”名称和列名,其中列名用于select语句。

邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助! 3w@live.cn