带有OUTPUT的INSERT,DELETE,UPDATE
摘自：http://www.it118.org/specials/c9fba99e-4401-49cf-8256-ac3c1a34c0d9/ca647129-a56f-4adb-a7fe-24ab58dc3f0a.htm
OUTPUT是SQL SERVER2005的新特性.可以从数据修改语句中返回输出.可以看作是"返回结果的DML".INSERT,DELETE,UPDATE均支持 OUTPUT子句.在OUTPUT子句中,可以引用特殊表inserted和deleted.使用inserted和deleted表与在触发器中使用的 非常相似.
输出方式:
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.
1 USE AdventureWorks;
2 GO
3
4 CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
5
6 DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))
7
8 INSERT TestTable (ID, TEXTVal)
9 OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
10 VALUES (1,'FirstVal')
11 INSERT TestTable (ID, TEXTVal)
12 OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
13 VALUES (2,'SecondVal')
14
15 SELECT * FROM @TmpTable
16 SELECT * FROM TestTable
17
18 DROP TABLE TestTable
19 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的应用
1 USE AdventureWorks;
2 GO
3 CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
4
5 DECLARE @TmpTable TABLE (ID_New INT, TEXTVal_New VARCHAR(100),ID_Old INT, TEXTVal_Old VARCHAR(100))
6
7 INSERT TestTable (ID, TEXTVal)
8 VALUES (1,'FirstVal')
9 INSERT TestTable (ID, TEXTVal)
10 VALUES (2,'SecondVal')
11
12 UPDATE TestTable
13 SET TEXTVal = 'NewValue'
14 OUTPUT Inserted.ID, Inserted.TEXTVal, 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
