Table-level parsing and synchronization for T-SQL

Question

I am interested in the T-SQL source code for synchronizing a table (or, possibly, a subset of it) with data from another similar table. Two tables can 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 would like to be able

• check that the two tables have matching columns: the source table has exactly the same columns as the base table, and the data types correspond
• make diff from base table to source table
• make the necessary updates, delete and paste to modify the data in the base table to match the original table
• optionally restricts diff to a subset of the base table,

. - ?

Answer 1

.

EXCEPT - INTERSECT

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

,

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

Answer 2

SQL Server 2008 . , .

@base @source. @base, 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 3

I’m not sure that it is applicable to your specific situation, but this type of operation is usually and relatively easy to perform using external tools (SQL Workbench diff, SQL Compare, etc.). It may even be scripted, it may not be called by the T-SQL procedure.