SQL Server 2008 - MERGE statement

SQL server 2008 has introduced a new feature to merge two tables. It lets you write a single SQL statement to insert, update and delete the records in the target table based on some conditions. It is very useful where you need to sync two tables. It avoids writing separate statements and logic for insert, update and delete.

It's syntax is very simple and straight forward:

MERGE <target_table> [AS table_alias]

USING <table_source> [AS table_alias]

ON <search_condition>

[WHEN MATCHED [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> ];

Let me write an example and explain how MERGE works.

Let's say we have two tables named Books_Source and Books_Target which need to be synced periodically. And at one instance

The table Books_Source contains:

BookID          BookName                         Quantity

1              "Learn ASP.Net"                      3

3              "SQL tips"                           5

4          "Complete Reference of JAVA"             2

The table Books_Target contains:

BookID     BookName                      Quantity

1         "Learn ASP.Net"                   2

2          "Learn XML"                      4

4      "Complete Reference of JAVA"         4

To merge the above tables our MERGE statement goes like this:

MERGE Books_Target AS t

USING Books_Source AS s

ON t.BookID = s.BookID

WHEN MATCHED AND s.Quantity != t.Qunatity

THEN UPDATE SET t.Quantity = s.Quantity

WHEN NOT MATCHED BY TARGET

THEN INSERT(BookID, BookName, Quantity) VALUES(s.BookID, s.BookName, s.Quantity)

WHEN NOT MATCHED BY SOURCE

THEN DELETE;

• As you can see in the above code, insert, update and delete actions happen in a single MERGE statement. It lets us add our own conditions to merge conflicts and our own merge actions to take place.
• In this example we have written update statement where the records exist in both tables but the column Quantity is different. In this case I want to update only when quantity gets changed in Order_Source table.
• Similarly we have written insert statement where the records exist in source table but not in target table. Here we have not added any additional condition because we wanted to insert all those don't exist in target table.
• In the same way we have written DELETE statement where the records exist in target table but not in the source table. In this case we don't want the records those are not in source table.
• MERGE statement is more efficient than separate statements for insert, update and delete operation to merge tables
  

The final result after the execution of above statement is:

BookID     BookName                      Quantity
1         "Learn ASP.Net"                   3
3              "SQL tips"                   5
4      "Complete Reference of JAVA"         2