Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Example of MERGE in SQL Server 2008

MERGE     Production.ProductInventory AS [pi]
USING     (
          SELECT ProductID,
                          SUM(OrderQty) AS OrderQty
               FROM       Sales.SalesOrderDetail AS sod
               INNER JOIN Sales.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID
               WHERE      soh.OrderDate = GETDATE()
               GROUP BY   ProductID
          ) AS src (ProductID, OrderQty) ON src.ProductID = [pi].ProductID
WHEN      MATCHED AND src.OrderQty = 0
           THEN DELETE; 
WHEN      MATCHED
               THEN UPDATE SET [pi].Quantity = src.OrderQty
WHEN      NOT MATCHED
               THEN INSERT VALUES (src.ProductID, src.OrderQty)
 

Print | posted on Thursday, September 20, 2007 10:31 AM | Filed Under [ SQL Server 2008 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET