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;