将MERGE语句的执行结果插入到另一个表中

下面的示例捕获从 MERGE 语句的 OUTPUT 子句返回的数据，并将该数据插入到另一个表中。 MERGE 语句根据在 SalesOrderDetail 表中处理的订单更新 ProductInventory 表的 Quantity 列。 本示例捕获已更新的行并将这些行插入到用于跟踪库存变化的另一个表中。

下面的示例捕获从 MERGE 语句的 OUTPUT 子句返回的数据，并将该数据插入到另一个表中。 MERGE 语句根据在 SalesOrderDetail 表中处理的订单更新 ProductInventory 表的 Quantity 列。 本示例捕获已更新的行并将这些行插入到用于跟踪库存变化的另一个表中。

USE AdventureWorks2012;
GO
CREATE TABLE Production.UpdatedInventory
 (ProductID INT NOT NULL, LocationID int, NewQty int, PreviousQty int,
 CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (ProductID, LocationID));
GO
INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty 
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 BETWEEN '20030701' AND '20030731'
 GROUP BY ProductID) AS src (ProductID, OrderQty)
 ON pi.ProductID = src.ProductID
 WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0 
 THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
 WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0 
 THEN DELETE
 OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)
 AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';
GO