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
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是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;
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
注意代码中被注释掉的第二个OUTPUT子句,后面没有INTO子句.如果还要输出返回给调用方,取消注释即可.这样,INSERT语句将包含两个OUTPUT子句.
示例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 .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
三.带有OUTPUT的UPDATE的应用
- USE AdventureWorks;
- GO
- CREATE TABLE TestTable (IDINT, TEXTValVARCHAR(100))
- DECLARE @TmpTable TABLE (ID_NewINT, TEXTVal_NewVARCHAR(100),ID_OldINT, TEXTVal_OldVARCHAR(100))
- INSERT TestTable (ID, TEXTVal)
- VALUES (1,'FirstVal')
- INSERT TestTable (ID, TEXTVal)
- VALUES (2,'SecondVal')
- UPDATE TestTable
- SET TEXTVal = 'NewValue'
- OUTPUT Inserted.ID, Inserted.TEXTVal, Deleted.ID, Deleted.TEXTValINTO @TmpTable
- WHERE ID IN (1,2)
-
- SELECT * FROM @TmpTable
- SELECT * FROM TestTable
- DROP TABLE TestTable
- GO
参考网址:http://www.cnblogs.com/sarahc/archive/2011/02/25/1964845.html
---------------------- Windows Phone 7手机开发、.Net培训、期待与您交流! -----------