Table-level diff and sync procedure for T-SQL

Question

I'm interested in T-SQL source code for synchronizing a table (or perhaps a subset of it) with data from another similar table. The two tables could contain any variables, for example I could have

 base table    source table 
 ==========    ============
 id     val    id       val
 ----------    ------------
 0        1    0          3
 1        2    1          2
 2        3    3          4

or

 base table             source table 
 ===================    ==================
 key    val1    val2    key   val1    val2
 -------------------    ------------------
 A         1       0    A        1       1  
 B         2       1    C        2       2
 C         3       3    E        4       0

or any two tables containing similar columns with similar names. I'd like to be able to

• check that the two tables have matching columns: the source table has exactly the same columns as the base table and the datatypes match
• make a diff from the base table to the source table
• do the necessary updates, deletes and inserts to change the data in the base table to correspond the source table
• optionally limit the diff to a subset of the base table,

preferrably with a stored procedure. Has anyone written a stored proc for this or could you point to a source?

Answer 1

SQL Server 2008 features the new merge statement. It's very flexible, if a bit complex to write out.

As an example, the following query would synchronize the @base and @source tables. It's limited to a subset of @base where id <> 2:

MERGE @base as tgt
USING @source as src
ON tgt.id = src.id and tgt.val = src.val
WHEN NOT MATCHED BY TARGET
    THEN INSERT (id, val) values (src.id, src.val)
WHEN NOT MATCHED BY SOURCE AND tgt.id <> 2
    THEN DELETE

Answer 2

Interesting question.

you could start from EXCEPT - INTERSECT

http://msdn.microsoft.com/en-us/library/ms188055.aspx

Here is readymade solution, may help you

http://www.sqlservercentral.com/scripts/Miscellaneous/30596/