If you are faced by a scenario where you typically have to sync up 2 tables by comparing source, destination most of the times and writing Insert, Delete and Update statements to achieve that, SQL Server 2008 introduces MERGE feature which is very powerful and fairly easy to achieve such sync up issues quick.
Example - Products dimension table has information about the products; you need to sync-up this table with the latest information about the products from the source table. You would need to write separate INSERT, UPDATE and DELETE statements to refresh the target table with an updated product list or do lookups. Though it seems to be straight forward at first glance, but it becomes cumbersome when you have do it very often or on multiple tables, even the performance degrades significantly with this approach. In the article below the author walks you through how to use the MERGE statement and do this in one pass. :)
USE - The MERGE statement basically merges data from a source result set to a target table based on a condition that you specify and if the data from the source already exists in the target or not. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record. The new MERGE SQL command looks like as below:
SYNTAX - MERGE[AS TARGET]
USING[AS SOURCE]
ON
[WHEN MATCHED
THEN]
[WHEN NOT MATCHED [BY TARGET]
THEN]
[WHEN NOT MATCHED BY SOURCE
THEN];
Nice and easy article for quick reference on MERGE - http://www.mssqltips.com/tip.asp?tip=1704
Thanks - D
Example - Products dimension table has information about the products; you need to sync-up this table with the latest information about the products from the source table. You would need to write separate INSERT, UPDATE and DELETE statements to refresh the target table with an updated product list or do lookups. Though it seems to be straight forward at first glance, but it becomes cumbersome when you have do it very often or on multiple tables, even the performance degrades significantly with this approach. In the article below the author walks you through how to use the MERGE statement and do this in one pass. :)
USE - The MERGE statement basically merges data from a source result set to a target table based on a condition that you specify and if the data from the source already exists in the target or not. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record. The new MERGE SQL command looks like as below:
SYNTAX - MERGE
USING
ON
[WHEN MATCHED
THEN
[WHEN NOT MATCHED [BY TARGET]
THEN
[WHEN NOT MATCHED BY SOURCE
THEN
Nice and easy article for quick reference on MERGE - http://www.mssqltips.com/tip.asp?tip=1704
Thanks - D
No comments:
Post a Comment