sql server 带有OUTPUT的INSERT,DELETE,UPDATE

原文: sql server 带有OUTPUT的INSERT,DELETE,UPDATE OUTPUT是SQL SERVER2005的新特性.可以从数据修改语句中返回输出.可以看作是"返回结果的DML".INSERT,DELETE,UPDATE均支持OUTPUT子句.在OUTPUT子句中,可以引用特殊表inserted和deleted.使用inserted和deleted表与在触发器中使用的非常相似. 

在INSERT,DELETE,UPDATE中OUTPUT的区别 

1.对于INSERT,可以引用inserted表以查询新行的属性.

2.对于DELETE,可以引用deleted表以查询旧行的属性.

3.对于UPDATE,使用deleted表查询被更新行在更改前的属性,用inserted表标识被更新行在更改后的值.  

输出方式: 

1.可以输出给调用方(客户端应用程序)

2.输出给表

3.两者皆可. 

应用:  

一.带有OUTPUT的INSERT的应用 

对于包含自增列的表执行多行insert语句,同时想知道新的标识值时,在INSERT中使用OUTPUT子句非常方便.对于单行INSERT语句,这不成问题:SCOPE_IDENTITY函数即可实现. 

1. -- Generating Surrogate Keys for Customers  
2. USE tempdb;  
3. GO  
4. IF OBJECT_ID('dbo.CustomersDim') IS NOT NULL  
5.   DROP TABLE dbo.CustomersDim;  
7. CREATE TABLE dbo.CustomersDim  
8. (  
9.   KeyCol      INT          NOT NULL IDENTITY PRIMARY KEY,  
10.   CustomerID  NCHAR(5)     NOT NULL,  
11.   CompanyName NVARCHAR(40) NOT NULL,  
12.   /* ... other columns ... */  
13. );  
14. -- Insert New Customers and Get their Surrogate Keys  
15. DECLARE @NewCusts TABLE  
17.   CustomerID NCHAR(5) NOT NULL PRIMARY KEY,  
18.   KeyCol     INT      NOT NULL UNIQUE  
20. INSERT INTO dbo.CustomersDim(CustomerID, CompanyName)  
21.     OUTPUT inserted.CustomerID, inserted.KeyCol  
22.     INTO @NewCusts  
23.     -- OUTPUT inserted.CustomerID, inserted.KeyCol  
24.   SELECT CustomerID, CompanyName  
25.   FROM Northwind.dbo.Customers  
26.   WHERE Country = N'UK';  
27. SELECT CustomerID, KeyCol FROM @NewCusts;  

注意代码中被注释掉的第二个OUTPUT子句,后面没有INTO子句.如果还要输出返回给调用方,取消注释即可.这样,INSERT语句将包含两个OUTPUT子句. 

示例2. 

1. USE AdventureWorks;  
2. GO 
3. CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))  
4. DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))  
5. INSERT TestTable (ID, TEXTVal)  
6. OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable  
7. VALUES (1,'FirstVal')  
10. VALUES (2,'SecondVal')  
11. SELECT * FROM @TmpTable  
12. SELECT * FROM TestTable  
13. DROP TABLE TestTable  

5. OUTPUT Inserted.ID, Inserted.TEXTVal  

二.带有OUTPUT的DELETE的应用. 

如果要删除数据的同时,还需要记录日志,或者归档数据.在DELETE中使用OUTPUT子句在适合不过了. 

9. DELETE  
10. FROM TestTable  
11. OUTPUT Deleted.ID, Deleted.TEXTVal INTO @TmpTable  
12. WHERE ID IN (1,2)  

三.带有OUTPUT的UPDATE的应用  

4. DECLARE @TmpTable TABLE (ID_New INT, TEXTVal_New VARCHAR(100),ID_Old INT, TEXTVal_Old VARCHAR(100))  
9. UPDATE TestTable  
10. SET TEXTVal = 'NewValue'  
11. OUTPUT Inserted.ID, Inserted.TEXTVal, Deleted.ID, Deleted.TEXTVal INTO @TmpTable  
16. GO