SQL Server 2008中SQL增强之一:Values新用途
SQL Server 2008中新增功能:可以使用单个Insert命令插入多行。
[ruby] view plaincopyprint?
-
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 plaincopyprint?
-
–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语句。