Thursday, June 7, 2012

Merge IN SQL 2008

I recently came across a scenario where i need to run a loop to update/insert multiple records into a existing table. There's a very easy and clean way of doing so in SQL 2008, with the 'Merge' method, it is achievable in just a couple lines of codes.

What i did was:

Step1 : Create a temp table

CREATE TABLE #Stock  -- source
  ProductCode Varchar(50) NOT NULL PRIMARY KEY,
  Quantity INT NOT NULL,
    CONSTRAINT Quantity_Default_2 DEFAULT 0
--Insert Record into the temp table for later update merge
Insert INTO #Stock(ProductCode, Quantity)
Select ProductCode, Quantity FROM dbo.Products WHERE Category = 'blahblah'

Step2: Merge temp table with target table
--Merge with target table

Merge [integration_products] ip
USING #Stock ms
ON ip.ProductCode = ms.ProductCode
WHEN Matched THEN   -- Matching Case DO update
SET ip.QtyOnhand = ms.Quantity + ip.QtyOnhand
WHEN Not Matched By Target THEN  --Insert
    INSERT ([ProductCode],[Status],[Category],[MinQuantity],[MaxQuantity],[CustOrders],[CustBackOrders],[SuppOrders],[QtyOnHand])
      VALUES (ms.ProductCode, ms.[Status], ms.[Category], 0,0,0,0,0,ms.Quantity);