Merge Statement in SQL Server 2008

SQL Server 2008 introduces the MERGE statement which will allow users to perform insert, update and delete operations in a single statement. In the earlier versions of SQL Server to achieve the same functionality the database developer or database administrator needed to write separate statements to perform the insert, update or delete of data in one table based on certain conditions in another table.

Using MERGE statement which Microsoft has introduced with SQL Server 2008 database developers or DBA’s can achieve the same functionality by writing very less TSQL code. The code written using this logic will also have performance issue due to the complexity of joins etc. Even though this feature is introduced very late in SQL Server Product, going forward I am very sure that it will be adopted very quickly in many data warehouse projects.

One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

How does MERGE Statement Internally Works
The MERGE statement internally works as an individual insert, update and delete statement within a single Merge statement. You need to specify the SOURCE and the TARGET table or query which should be joined together. Within the MERGE statement you also need to specify the type of the data modification that needs to be performed when the records between the source and target are matched and what actions needs to be performed when they are not matched. With the introduction of MERGE statement the complex TSQL codes which was used earlier to do checks for the existence or inexistence of data within the data warehouse can be replaced with single Merge statement. The use of Merge statement will also improve the query performance.
Below are the three different matched clauses in MERGE:

• WHEN MATCHED THEN
• Rows that meet the criteria
• WHEN [TARGET] NOT MATCHED THEN
• Rows that do not match with another row in the target table
• WHEN SOURCE NOT MATCHED THEN
• Rows that do not match with another row in the source table

Syntax of MERGE statement is as following:
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( ) ] [ [ AS ] table_alias]
USING
ON
[ WHEN MATCHED [ AND ]
THEN ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND ]
THEN ]
[ WHEN NOT MATCHED BY SOURCE [ AND ]
THEN ]
[ ]
[ OPTION ( [ ,...n ] ) ]
;

Example

CREATE TABLE dbo.tbl_Source (id INT, name NVARCHAR(100), qty INT);

CREATE TABLE dbo.tbl_Target (id INT, name NVARCHAR(100), qty INT);

--Synchronize source data with target

MERGE INTO dbo.tbl_Target AS t

USING dbo.tbl_Source AS s

ON t.id = s.id

WHEN MATCHED AND (t.name != s.name OR t.qty!= s.qty) THEN

--Row exists and data is different

UPDATE SET t.name = s.name, t.qty = s.qty

WHEN NOT MATCHED THEN

--Row exists in source but not in target

INSERT INTO (id, name, qty)

VALUES (s.id, s.name, s.qty)

WHEN SOURCE NOT MATCHED THEN

--Row exists in target but not in source, Then Delete from Target

DELETE ;

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