MERGE Changes in Microsoft SQL Server 2008

In SQL Server 2008, you can perform insert, update, or delete operations in a
single statement using the MERGE statement. MERGE statement allows you to join a
data source with a target table or view, and then perform multiple actions
against the target based on the results of that join. Let's explore this.

Let's say your business sells products. Now let's create two tables, Product
table to store the product details and Sale table to store sales transactions:

CREATE TABLE Product

(

ProductID int,

ProductDescription nvarchar(20),

quantity int

)

CREATE TABLE Sale

(

ProductID int,

quantity int

)

Let us add a few products to take the discussion ahead.

INSERT INTO Product VALUES(1, 'WaterBottle', 20)

INSERT INTO Product VALUES(2, 'Gloves', 10)

INSERT INTO Product VALUES(3, 'Jersey', 30)

INSERT INTO Product VALUES(4, 'Cap', 40)

Now every time we make a sale we make an entry in the Sale table like this:

INSERT INTO Sale VALUES(1, 5)

INSERT INTO Sale VALUES(4, 5)

INSERT INTO Sale VALUES(2, 10)

These sales would change the quantity in the Product table, right? What do
you do if you want to update the stock in the Product table after all the sales
have been made? We would write a cursor to loop through the records in the Sale
table and update the Product table for every row, right? However, the new MERGE
statement provided with SQL Server 2008 makes this simpler. We can achieve what
we desire with a single statement like this

:

MERGE Product

USING Sale

ON Product.ProductID = Sale.ProductID

WHEN MATCHED THEN

UPDATE SET Product.quantity -= Sale.quantity;

Well, here is the result.

Could we have achieved the same thing by running an UPDATE statement with a
join? Let's try that out. Truncate the data in the tables so we can start from
scratch:

TRUNCATE TABLE Product

TRUNCATE TABLE Sale

Re-run the initial INSERT statements. Now let us try the UPDATE:

UPDATE Product

SET Product.quantity -= Sale.quantity

FROM Product INNER JOIN Sale

ON Product.ProductID = Sale.ProductID

The result is the same.

Well, what if the case is a bit more complex? If the product quantity drops
to zero and we want to delete that product from the Product table. Let us start
everything from scratch again:

TRUNCATE TABLE Product

TRUNCATE TABLE Sale

INSERT INTO Product VALUES(1, 'WaterBottle', 20)

INSERT INTO Product VALUES(2, 'Gloves', 10)

INSERT INTO Product VALUES(3, 'Jersey', 30)

INSERT INTO Product VALUES(4, 'Cap', 40)

INSERT INTO Sale VALUES(1, 5)

INSERT INTO Sale VALUES(4, 5)

INSERT INTO Sale VALUES(2, 10)

Let us make a small change to the MERGE statement so that it does what we
want it to do:

MERGE Product

USING Sale

ON Product.ProductID = Sale.ProductID

WHEN MATCHED AND Product.quantity = Sale.quantity

THEN

DELETE

WHEN MATCHED THEN

UPDATE SET Product.quantity -= Sale.quantity;

Now check the output now.

This is what we wanted to do. And if you want to achieve all this in one
single statement, there is no way other than using MERGE. You must have learned
by now that you can write more conditions into the MERGE statement than the two
conditions I have demonstrated.

Amaresh Patnaik