原文:
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函数即可实现.
-
-
USE tempdb;
-
GO
-
IF OBJECT_ID('dbo.CustomersDim') IS NOT NULL
-
DROP TABLE dbo.CustomersDim;
-
GO
-
-
CREATE TABLE dbo.CustomersDim
-
(
-
KeyCol INT NOT NULL IDENTITY PRIMARY KEY,
-
CustomerID NCHAR(5) NOT NULL,
-
CompanyName NVARCHAR(40) NOT NULL,
-
/* ... other columns ... */
-
);
-
-
-
DECLARE @NewCusts TABLE
-
(
-
CustomerID NCHAR(5) NOT NULL PRIMARY KEY,
-
KeyCol INT NOT NULL UNIQUE
-
);
-
-
INSERT INTO dbo.CustomersDim(CustomerID, CompanyName)
-
OUTPUT inserted.CustomerID, inserted.KeyCol
-
INTO @NewCusts
-
-
SELECT CustomerID, CompanyName
-
FROM Northwind.dbo.Customers
-
WHERE Country = N'UK';
-
-
SELECT CustomerID, KeyCol FROM @NewCusts;
-
GO
注意代码中被注释掉的第二个OUTPUT子句,后面没有INTO子句.如果还要输出返回给调用方,取消注释即可.这样,INSERT语句将包含两个OUTPUT子句.
示例2.
-
USE AdventureWorks;
-
GO
-
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
-
-
DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))
-
-
INSERT TestTable (ID, TEXTVal)
-
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
-
VALUES (1,'FirstVal')
-
INSERT TestTable (ID, TEXTVal)
-
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
-
VALUES (2,'SecondVal')
-
-
SELECT * FROM @TmpTable
-
SELECT * FROM TestTable
-
-
DROP TABLE TestTable
-
GO
-
USE AdventureWorks;
-
GO
-
-
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
-
-
INSERT TestTable (ID, TEXTVal)
-
OUTPUT Inserted.ID, Inserted.TEXTVal
-
VALUES (1,'FirstVal')
-
INSERT TestTable (ID, TEXTVal)
-
OUTPUT Inserted.ID, Inserted.TEXTVal
-
VALUES (2,'SecondVal')
-
-
DROP TABLE TestTable
-
GO
二.带有OUTPUT的DELETE的应用.
如果要删除数据的同时,还需要记录日志,或者归档数据.在DELETE中使用OUTPUT子句在适合不过了.
-
USE AdventureWorks;
-
GO
-
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
-
-
DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))
-
-
INSERT TestTable (ID, TEXTVal)
-
VALUES (1,'FirstVal')
-
INSERT TestTable (ID, TEXTVal)
-
VALUES (2,'SecondVal')
-
-
DELETE
-
FROM TestTable
-
OUTPUT Deleted.ID, Deleted.TEXTVal INTO @TmpTable
-
WHERE ID IN (1,2)
-
-
SELECT * FROM @TmpTable
-
SELECT * FROM TestTable
-
-
DROP TABLE TestTable
-
GO
三.带有OUTPUT的UPDATE的应用
-
USE AdventureWorks;
-
GO
-
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
-
-
DECLARE @TmpTable TABLE (ID_New INT, TEXTVal_New VARCHAR(100),ID_Old INT, TEXTVal_Old VARCHAR(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.TEXTVal INTO @TmpTable
-
WHERE ID IN (1,2)
-
-
SELECT * FROM @TmpTable
-
SELECT * FROM TestTable
-
-
DROP TABLE TestTable
-
GO