MERGE Statement in SQL Server 2008

Lot of articles available in MERGE statement on net. I have also tried to use this for my purpose and post here some code for reference. May this help.


Using MERGE statement we can do INSERT, UPDATE and DELETE in one query on the bases of condition.


Here i have created 2 table called TestArchive and TestFeed. We can try here INSERT, UPDATE and DELETE command using MERGE statement.


CREATE TABLE TestArchive
 (
       CustomerID INT PRIMARY KEY,
       SalesDate INT,
       TotalSalesAmount MONEY,
 )    


 CREATE TABLE TestFeed
 (
       CustomerID INT,
       Product VARCHAR(10),
       SaleAmount MONEY
 )


 INSERT INTO TestFeed
 (CustomerID, Product, SaleAmount)
 VALUES
 (1,'PoolTable', 1000),
 (2,'BigScreen', 955),
 (3,'Computer', 590),
 (4,'BigScreen', 880),
 (5,'Computer', 700)


Now I am going to insert data into TestArchive table using MERGE.


MERGE TestArchive AS SA
 USING (
       SELECT
             CustomerID,
             LoadDate = MIN(CONVERT(VARCHAR(8), GETDATE(), 112)),
             TotalSalesAmount = SUM(SaleAmount)
       FROM SalesFeed
       GROUP BY CustomerID
 ) AS TestFeedCTE (CustomerID, LoadDate, TotalSalesAmount)
 ON
 (
 SA.CustomerID = TestFeedCTE.CustomerID AND SA.SalesDate = TestFeedCTE.LoadDate
 )
 WHEN NOT MATCHED THEN
       INSERT (CustomerID, SalesDate, TotalSalesAmount)
       VALUES( TestFeedCTE.CustomerID, TestFeedCTE.LoadDate, TestFeedCTE.TotalSalesAmount)
 WHEN MATCHED THEN
       UPDATE
        SET SA.TotalSalesAmount = SA.TotalSalesAmount + TestFeedCTE.TotalSalesAmount;


Here you can see all 5 rows inserted into TestArchive table, if we run again this Merge statement then Total sales amount would be double.


We can put one more condition with MATCHED like WHEN MATCHED  and SA.TotalSalesAmount < 2000


MERGE TestArchive AS SA
 USING (
       SELECT
             CustomerID,
             LoadDate = MIN(CONVERT(VARCHAR(8), GETDATE(), 112)),
             TotalSalesAmount = SUM(SaleAmount)
       FROM SalesFeed
       GROUP BY CustomerID
 ) AS TestFeedCTE (CustomerID, LoadDate, TotalSalesAmount)
 ON
 (
 SA.CustomerID = TestFeedCTE.CustomerID AND SA.SalesDate = TestFeedCTE.LoadDate
 )
 WHEN NOT MATCHED THEN
       INSERT (CustomerID, SalesDate, TotalSalesAmount)
       VALUES( TestFeedCTE.CustomerID, TestFeedCTE.LoadDate, TestFeedCTE.TotalSalesAmount)
 WHEN MATCHED  and SA.TotalSalesAmount < 2000  THEN
       UPDATE
        SET SA.TotalSalesAmount = SA.TotalSalesAmount + TestFeedCTE.TotalSalesAmount;


We can add DELETE condition in the same statement.


MERGE TestArchive AS SA
 USING (
       SELECT
             CustomerID,
             LoadDate = MIN(CONVERT(VARCHAR(8), GETDATE(), 112)),
             TotalSalesAmount = SUM(SaleAmount)
       FROM SalesFeed
       GROUP BY CustomerID
 ) AS TestFeedCTE (CustomerID, LoadDate, TotalSalesAmount)
 ON
 (
 SA.CustomerID = TestFeedCTE.CustomerID AND SA.SalesDate = TestFeedCTE.LoadDate
 )
 WHEN NOT MATCHED THEN
       INSERT (CustomerID, SalesDate, TotalSalesAmount)
       VALUES( TestFeedCTE.CustomerID, TestFeedCTE.LoadDate, TestFeedCTE.TotalSalesAmount)
 WHEN MATCHED  and SA.TotalSalesAmount < 2000  THEN
       UPDATE
        SET SA.TotalSalesAmount = SA.TotalSalesAmount + TestFeedCTE.TotalSalesAmount
 WHEN MATCHED  and SA.TotalSalesAmount > 2000  THEN
       DELETE;


Suggestions always welcome.
Thanks.