Insert the execution result of the MERGE statement into another table.

The following example captures the data returned from the OUTPUT clause of the MERGE statement and inserts the data into another table. The MERGE statement updates the Quantity column of the ProductInventory table based on the order processed in the SalesOrderDetail table. This example captures updated rows and inserts them into another table used to track inventory changes.

The following example captures the data returned from the OUTPUT clause of the MERGE statement and inserts the data into another table. The MERGE statement updates the Quantity column of the ProductInventory table based on the order processed in the SalesOrderDetail table. This example captures updated rows and inserts them into another table used to track inventory changes.

The following example captures the data returned from the OUTPUT clause of the MERGE statement and inserts the data into another table. The MERGE statement updates the Quantity column of the ProductInventory table based on the order processed in the SalesOrderDetail table. This example captures updated rows and inserts them into another table used to track inventory changes.

<无>

USE AdventureWorks2012;GOCREATE TABLE Production.UpdatedInventory    (ProductID INT NOT NULL, LocationID int, NewQty int, PreviousQty int,     CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (ProductID, LocationID));GOINSERT INTO Production.UpdatedInventorySELECT 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