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