带有OUTPUT的INSERT,DELETE,UPDATE

---------------------- Windows Phone 7手机开发、.Net培训、期待与您交流！ -----------

带有OUTPUT的INSERT,DELETE,UPDATE

1.'dbo.CustomersDim' ) IS NOT NULL  

2 .  DROP TABLE dbo.CustomersDim;  

3 .GO  

4 .  

5 .CREATE TABLE dbo.CustomersDim  

6 .(  

7 .  KeyCol      INT          NOT NULL IDENTITY PRIMARY KEY,  

8.  CustomerID  NCHAR(5 )     NOT NULL,  

9.  CompanyName NVARCHAR(40 ) NOT NULL,  

10.  /* ... other columns ... */   

11 .);  

12 .  

13.--  Insert New Customers and Get their Surrogate Keys  


14 .DECLARE @NewCusts TABLE  

15 .(  

16.  CustomerID NCHAR(5 ) NOT NULL PRIMARY KEY,  

17 .  KeyCol     INT      NOT NULL UNIQUE  

18 .);  

19 .  

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 .  

28 .SELECT CustomerID, KeyCol FROM @NewCusts;  

29.GO  

带有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. 
   
2. 

   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;  
   
   6 .GO  
   
   7 .  
   
   8 .CREATE TABLE dbo.CustomersDim  
   
   9 .(  
   
   10 .  KeyCol      INT          NOT NULL IDENTITY PRIMARY KEY,  
   
   11 .  CustomerID  NCHAR( 5 )     NOT NULL,  
   
   12 .  CompanyName NVARCHAR( 40 ) NOT NULL,  
   
   13 .   /*  ... other columns ...  */   
   
   14 .);  
   
   15 .  
   
   16 . --  Insert New Customers and Get their Surrogate Keys  
   
   
   17 .DECLARE @NewCusts TABLE  
   
   18 .(  
   
   19 .  CustomerID NCHAR( 5 ) NOT NULL PRIMARY KEY,  
   
   20 .  KeyCol     INT      NOT NULL UNIQUE  
   
   21 .);  
   
   22 .  
   
   23 .INSERT INTO dbo.CustomersDim(CustomerID, CompanyName)  
   
   
   24 .    OUTPUT inserted.CustomerID, inserted.KeyCol  
   
   
   25 .    INTO @NewCusts  
   
   26 .     --  OUTPUT inserted.CustomerID, inserted.KeyCol  
   
   
   27 .  SELECT CustomerID, CompanyName  
   
   28 .  FROM Northwind.dbo.Customers  
   
   29 .  WHERE Country  =  N ' UK ' ;  
   
   30 .  
   
   31 .SELECT CustomerID, KeyCol FROM @NewCusts;  
   
   32 .GO  

   
3. 
   

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

示例2.

1. 
   
2. View Code

   

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

   
   
   

二.带有OUTPUT的DELETE的应用.

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

1. 
   
2. 

   1 .USE AdventureWorks;  
   
   2 .GO  
   
   3 .CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR( 100 ))  
   
   4 .
   5 .DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR( 100 ))  
   
   6 .
   7 .INSERT TestTable (ID, TEXTVal)  
   
   8 .VALUES ( 1 , ' FirstVal ' )  
   
   9 .INSERT TestTable (ID, TEXTVal)  
   
   10 .VALUES ( 2 , ' SecondVal ' )  
   
   11 . 
   
   12 .DELETE  
   
   13 .FROM TestTable  
   
   14 .OUTPUT Deleted.ID, Deleted.TEXTVal INTO @TmpTable  
   
   15 .WHERE ID IN ( 1 , 2 )   
   16 . 
   
   17 .SELECT  *  FROM @TmpTable  
   
   18 .SELECT  *  FROM TestTable  
   
   19 .
   20 .DROP TABLE TestTable  
   
   21 .GO  

   
3. 
   

三.带有OUTPUT的UPDATE的应用 

1. 
   
2. USE AdventureWorks; 
   
3. GO 
   
4. CREATE TABLE TestTable (IDINT, TEXTValVARCHAR(100)) 
   
5. 
   
6. DECLARE @TmpTable TABLE (ID_NewINT, TEXTVal_NewVARCHAR(100),ID_OldINT, TEXTVal_OldVARCHAR(100)) 
   
8. INSERT TestTable (ID, TEXTVal) 
   
9. VALUES (1,'FirstVal') 
   
10. INSERT TestTable (ID, TEXTVal) 
    
11. VALUES (2,'SecondVal') 
    
13. UPDATE TestTable 
    
14. SET TEXTVal = 'NewValue' 
    
15. OUTPUT Inserted.ID, Inserted.TEXTVal, Deleted.ID, Deleted.TEXTValINTO @TmpTable 
    
16. WHERE ID IN (1,2) 
    
17.  
    
18. SELECT * FROM @TmpTable 
    
19. SELECT * FROM TestTable 
    
21. DROP TABLE TestTable 
    
22. GO 

参考网址：http://www.cnblogs.com/sarahc/archive/2011/02/25/1964845.html

 

---------------------- Windows Phone 7手机开发、.Net培训、期待与您交流！ -----------