带有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函数即可实现.

    -- 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