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

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

$velocityCount

–> <!—ecms

–>

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.Product<strong style="color:transparent">来2源gaodaima#com搞(代@码&网</strong>ID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty) AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';GO

搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]‍，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：将 MERGE 语句的执行结果插入到另一个表中

Hi，您需要填写昵称和邮箱！