How to use MERGE statement in SQL Server 2008?

BANGALORE, INDIA: 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;

{#PageBreak#} 

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.