MERGE Statement in SQL Server 2008

There has been significant T-SQL improvements in SQL Server 2008. This post explores the most notable changes in T-SQL introduced in SQL Server 2008.

The MERGE Statement

As name says, It combines Insert,Update, and Delete operations along with the Select operation which provides the source and target data for merge. In earlier versions, We need to write separate multiple statements to achieve the same.

Advantage: Simple code easier to write and maintain than writing code using  separate statements. The MERGE Statement is particularly suited to Data Warehousing scenarios.

Example:

Let us take Stock and Trades tables as an example to demonstrate the MERGE statement feature. Create these two tables where Stock table contains the shares we own and Trades table contain share we make.

CREATE TABLE Stock(Symbol varchar(10) PRIMARY KEY, Qty int)
CREATE TABLE Trade(Symbol varchar(10) PRIMARY KEY, Delta int)

                      

Insert some shares in Stock Table

INSERT INTO Stock VALUES ('MSFT', 10)
INSERT INTO Stock VALUES ('WCP', 5)

Assume we bought 5 New shares for MSFT and sold 5 shares for WCP and bought 3 new NWT shares for trade.

These will be stored in Trade table as follows

INSERT INTO Trade VALUES('MSFT', 5)
INSERT INTO Trade VALUES('WCP', -5)
INSERT INTO Trade VALUES('NWT', 3)

The Contents of the two table are

We need to update the Quantities in Stock table to reflect the changes in Trade table. In Earlier versions we could have written a Join statement to detect the changes in Stock Table as result of change in the Trades table and perform Insert,Update and Delete operations to apply those changes to Stock Table.

All the above logic can now be performed with a single statement using MERGE statement.

MERGE Stock
 USING Trade
 ON Stock.Symbol = Trade.Symbol
 WHEN MATCHED AND (Stock.Qty + Trade.Delta = 0) THEN
   -- delete stock if entirely sold
   DELETE
 WHEN MATCHED THEN
   -- update stock quantity (delete takes precedence over update)
   UPDATE SET Stock.Qty += Trade.Delta
 WHEN NOT MATCHED BY TARGET THEN
   -- add newly purchased stock
  INSERT VALUES (Trade.Symbol, Trade.Delta);

The Statement begins with MERGE keyword, followed by USING and ON Keywords. The first table name after MERGE keyword is Target Table name and Second table after USING key word is Source table for MERGE operation.

NOTE: The semicolon at the end of query is mandatory and you will receive an error if you omit it.

Reference: Programming Microsoft SQL Server 2008 book

Share this post :