T-SQL 插入增强 (新特性)
/**********************************************************
Author="WZ"
Create Date="2008/9/7"
SQL SERVER 2008 开发系列(十)T-SQL 插入增强 (新特性)
***********************************************************/
USE Blog
GO
--创建测试数据表
CREATE TABLE Product
(
ID int NOT NULL,
Name varchar(200),
ListPrice money,
CONSTRAINT PK_Product PRIMARY KEY (ID)
)
GO
--更新测试表
CREATE TABLE ExpensiveProduct_CT
(
CT_ID int NOT NULL IDENTITY(1,1),
CT_DATE datetime NOT NULL DEFAULT(getdate()),
CT_OPERATION varchar(10),
ID int
)
GO
--插入多条数据,行构造
INSERT INTO Product VALUES (1, 'Office 2007 Professional', 2000),--第一行
(2, 'Office 2007 Enterprise', 5000),--第二行
(3, 'Office 2007 Ultimate', 8000)--第三行
GO
Select * from Product
--插入操作
INSERT INTO ExpensiveProduct_CT (CT_OPERATION, ID)
SELECT 'UPDATE' AS CT_OPERATION, t.ID
FROM
(
UPDATE Product SET ListPrice = ListPrice * 1.1 OUTPUT INSERTED.ID, INSERTED.ListPrice
)AS t
WHERE t.ListPrice > 5000
GO
SELECT * FROM Product
SELECT * FROM ExpensiveProduct_CT
GO