Tuesday, March 25, 2008

One very common requirement is to merge source data to a target table. For example, merge the daily changes to accounts data from branch office table to central accounts table. On SQL Server version 2005 and prior this was accomplished using separate INSERT and UPDATE statements. This involved checking if key column exists to perform update and insert if not, or attempt an update first and then if not rows were affected perform insert. Not to mention if we have to check if account is missing from the branch office data and needs to be deleted from the central accounts table. That way we end up with multiple (sometimes complex) statements to implement one transaction, accessing both source and target tables multiple times.

SQL Server 2008 offers a lot more elegant way using the MERGE statement (MERGE is supported by ANSI SQL). It makes data merging very simple and elegant, as well as efficient.

Next step is to enhance the statement including predicates to check and update only accounts that have changed. The only difference here is the additional conditions in the MATCHED clause, results will be the same as before.

There is a lot more to MERGE, but this just shows the tremendous power it provides for merging data.

Note:

This code has been tested with SQL Server 2008 CTP 6 (February 2008). As of SQL Server 2008 Release Candidate 0 (June 2008) the clause "WHEN SOURCE NOT MATCHED" has been replaced with "WHEN NOT MATCHED BY SOURCE". This makes the wording clearer. It is good to note that this clause of MERGE is not standard.

There is big difference between predicates in the ON clause and predicates in the MATCHED clause. The predicates in the ON clause define what is matched and what is not (that is which WHEN section will be executed). The predicates on the MATCHED clause simply filter the rows that will be affected by the action statement in the clause. Also, you can add predicates in the source/target tables (if you use derived tables or CTEs), which in effect will limit the sets before they are matched. Using the correct predicate placement depends on what you are trying to accomplish.