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