将merge执行的结果插入到另外一个表中

    我们还可以将merge操作得到的结果写入到另外一个表中，如下的语句将更新的每条数据信息写入到一个新建的表Production.UpdatedInventory中，代码如下：

INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty 
FROM
(    MERGE Production.ProductInventory AS target
     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 source (ProductID, OrderQty)
     ON target.ProductID = source.ProductID
    WHEN MATCHED AND target.Quantity – source.OrderQty >= 0 
        THEN UPDATE SET target.Quantity = target.Quantity – source.OrderQty
    WHEN MATCHED AND target.Quantity – source.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
    执行这个语句再查询表得到如下图5的结果，我们可以看到新的销售量总是比以前的销售量要少，因为执行一次就要减去订单量。

作者：Tyler Ning 
出处：http://www.cnblogs.com/tylerdonet/