Wednesday, January 18, 2012

Introduction to the MERGE statement in SQL Server 2008

The MERGE statement is a new feature in SQL Server 2008, it provides an efficient way to perform multiple DML operations in one statement. In previous versions of SQL Server, you had to write separate statements to INSERT, UPDATE or DELETE data based on certain conditions, but now, using the MERGE statement you can include the logic of such data modifications in one statement that even checks when the data is matched the just update it and when unmatched then insert it. One of the most important advantages of the MERGE statement is all the data is read and processed only once. In previous version three different statement has to be written to process three different activities (INSERT, UPDATE, DELETE), however using the MERGE statement all update activity can be done in one pass of the database table. This is quite an improvement in performance of the database query.

Syntax of the MERGE statement is as following:

MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ]  target_table [ WITH ( <merge_hint> )] [ [ AS ] table_alias ]
USING <table_source>
ON <merge_search_condition>
[ WHEN MATCHES [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
THEN <merge_not_matched> ]
[ WHEN NOT MATCHED [ BY SOURCE ] [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ <output_clause> ]
[ OPTION ( <query_hint> [ ,...n ] ) ]
;

Example:


Let's create a Product table and a Product_Used table and insert some records.

CREATE TABLE Products
(
 Product_ID INTEGER PRIMARY KEY
,Product_Name VARCHAR(20)
,Used CHAR(1)
)
GO
INSERT INTO Products
VALUES (1, 'Opel','Y')
INSERT INTO Products
VALUES (2,'Ford','N')
INSERT INTO Products
VALUES (3,'Volvo','Y')
INSERT INTO Products
VALUES (4,'BMW','Y')
INSERT INTO Products
VALUES (5,'Audi','Y')
GO
CREATE TABLE In_Stock
(
 Product_ID INTEGER REFERENCES Products
,In_Stock INTEGER
)
GO
INSERT INTO In_Stock
VALUES (1,100)
INSERT INTO In_Stock
VALUES (2,200)
INSERT INTO In_Stock
VALUES (3,300)
GO

In my example I will consider three main conditions while we merge this two tables.



  1. Delete records who are no longer used
  2. Update In_Stock add 100 to each if internal if record exists
  3. Insert the record if record doesn't exists

Now I will write the MERGE process for tables created earlier. I will make sure that we will have our three conditions discussed above are satisfied.

MERGE In_Stock AS t1
USING (SELECT Product_ID, Used FROM Products) AS t2
ON t1.Product_ID = t2.Product_ID
WHEN MATCHED AND t2.Used = 'N' THEN DELETE
WHEN
MATCHED THEN UPDATE SET t1.In_Stock = t1.In_Stock + 100
WHEN NOT MATCHED THEN
INSERT
(Product_ID,In_Stock)
VALUES(t2.Product_ID,100);
GO


The select before the MERGE statement looks like this:

Product_ID  Product_Name         Used
----------- -------------------- ----
1           Opel                 Y
2           Ford                 N
3           Volvo                Y
4           BMW                  Y
5           Audi                 Y

(5 row(s) affected)

Product_ID  In_Stock
----------- -----------
1           100
2           200
3           300

(3 row(s) affected)

The select after the MERGE statement looks like:

Product_ID  Product_Name         Used
----------- -------------------- ----
1           Opel                 Y
2           Ford                 N
3           Volvo                Y
4           BMW                  Y
5           Audi                 Y

(5 row(s) affected)

Product_ID  In_Stock
----------- -----------
1           200
4           100
3           400
5           100

(4 row(s) affected)


We can see that 5 statements have been executed, the In_stock for Product_ID 2 has been deleted. The In_Stock for Product_ID 1 and 3 have been updated. The In_stock for Product_ID 4 and 5 have been inserted.


There are two very important points to remember while using the merge statement:

  • Semicolon is mandatory after the MERGE statement.
  • When there is a match clause used along with some conditions, it has to be specific first among all other WHEN MATCH clause.

The MERGE statement is a very handy improvement for T-SQL developers who have to update the database tables with complicated logic. The MERGE statement also improves the performance of the database as it passes only once through the data.



Now I will write the MERGE process for tables created earlier. I will make sure that we will have our three conditions discussed above are satisfied.

No comments:

Post a Comment