Home Forums SQL Server 2008 T-SQL (SS2K8) Multiple DMLS in one when clause of MERGE Statement RE: Multiple DMLS in one when clause of MERGE Statement

  • I don't believe you can perform multiple DMLs from the MERGE.

    You can however perform an insert from the output of a merge.

    From msdn OUTPUT CLAUSE example K.

    "INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)

    SELECT ProductID, GETDATE()

    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 = '20070401'

    GROUP BY ProductID) AS src (ProductID, OrderQty)

    ON (pi.ProductID = src.ProductID)

    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0

    THEN DELETE

    WHEN MATCHED

    THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty

    OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)

    WHERE Action = 'DELETE'; "