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函式即可實現. 

-- Generating Surrogate Keys for Customers
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 ... */
);

-- Insert New Customers and Get their Surrogate Keys
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
    -- OUTPUT inserted.CustomerID, inserted.KeyCol
  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