SQL Server 2008中SQL增强之一：Values新用途

SQL Server 2008中SQL增强之一：Values新用途

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

[ruby] view plaincopyprint?

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)

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

[ruby] view plaincopyprint?

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. */

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