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

Not sure if it's of any use to your specific situation, but this kind of operation is usually and relatively easily done using external tools (SQL Workbench diff, SQL Compare etc.). It can even be scripted, just probably not invokable from a T-SQL procedure.

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/

Answer 3

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