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

SQL Server (2.1万) 2014-09-10 11:16:31

    我们还可以将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的结果,我们可以看到新的销售量总是比以前的销售量要少,因为执行一次就要减去订单量。

    将merge执行的结果插入到另外一个表中 (https://www.tiejiang.org/) SQL Server 第1张


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

THE END

发表评论