SQL Server（00）：带有OUTPUT的INSERT,DELETE,UPDATE

UTPUT是SQL SERVER2005的新特性.可以从数据修改语句中返回输出.可以看作是"返回结果的DML".INSERT,DELETE,UPDATE均支持OUTPUT子句.在OUTPUT子句中,可以引用特殊表inserted和deleted.使用inserted和deleted表与在触发器中使用的非常相似.

在INSERT,DELETE,UPDATE中OUTPUT的区别

对于INSERT,可以引用inserted表以查询新行的属性.
对于DELETE,可以引用deleted表以查询旧行的属性.
对于UPDATE,使用deleted表查询被更新行在更改前的属性,用inserted表标识被更新行在更改后的值.

输出方式:

输出给调用方(客户端应用程序)
输出给表

应用:

1、带有OUTPUT的INSERT的应用

对于包含自增列的表执行多行insert语句,同时想知道新的标识值时,在INSERT中使用OUTPUT子句非常方便。

1、对于单行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,  

);  

-- 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、多行INSERT语句

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

2、带有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

3、带有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

4、MERGE语句

下面的示例捕获从 OUTPUT 语句的 MERGE 子句返回的数据，并将这些数据插入另一个表。

MERGE 语句每天根据在 Quantity 表中处理的订单更新 ProductInventory 表的 SalesOrderDetail 列。如果产品的库存降至 0 或更低，它还会删除与这些产品对应的行。

本示例捕获已删除的行并将这些行插入另一个表 ZeroInventory 中，该表跟踪没有库存的产品。

USE AdventureWorks2012;  
GO  
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL  
    DROP TABLE Production.ZeroInventory;  
GO  
--Create ZeroInventory table.  
CREATE TABLE Production.ZeroInventory (DeletedProductID int, RemovedOnDate DateTime);  
GO  
  
INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)  
SELECT ProductID, GETDATE()  
FROM  
(   MERGE Production.ProductInventory AS pi  
    USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod  
           JOIN Sales.SalesOrderHeader AS soh  
           ON sod.SalesOrderID = soh.SalesOrderID  
           AND soh.OrderDate = '20070401'  
           GROUP BY ProductID) AS src (ProductID, OrderQty)  
    ON (pi.ProductID = src.ProductID)  
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0  
        THEN DELETE  
    WHEN MATCHED  
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty  
    OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)  
WHERE Action = 'DELETE';


IF @@ROWCOUNT = 0  
PRINT 'Warning: No rows were inserted';  
GO

SELECT DeletedProductID, RemovedOnDate FROM Production.ZeroInventory;

使用OUTPUT子句的注意事项：

以下语句中不支持 OUTPUT 子句：

引用本地分区视图、分布式分区视图或远程表的 DML 语句。
包含 EXECUTE 语句的 INSERT 语句。
不能将 OUTPUT INTO 子句插入视图或行集函数。
参数或变量作为 UPDATE 语句的一部分进行了修改，则 OUTPUT 子句将始终返回语句执行之前的参数或变量的值而不是已修改的值

参考：

https://docs.microsoft.com/zh-cn/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15&viewFallbackFrom=sql-server-2014