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
