Using the Merge statement in SQL Server to synchronize the contrast between table data

There are many ways to implement synchronization between table data, such as deleting and then insert again, or writing some other branch conditions to determine whether to insert or UPDATE. The synchronization between table data can also be achieved through a combination of various tasks such as Lookup, Condition Split, and so on in SSIS Package. "Sync" here means that every time you execute a piece of code, you can ensure that the data in table A and table B are always the same.

Can be implemented through the Merge statements provided in SQL Server, and the details of the operation can also be recorded. For specific details please refer to-http://msdn.microsoft.com/zh-cn/library/bb510625.aspx I'll just use a simple example to describe some of its common features.

Test table-A Source table, a Target table, and a logging table to record each action performed.

The following are the main synchronization operations

Merge into-the destination of the data, the table object that the data is eventually MERGE into

USING the condition associated with the source table connection on

When matched-if the match succeeds, the association condition succeeds (you should update all other field values in SOURCE to the TARGET table)

When notmatched by TARGET-if the match is unsuccessful (there is no record in target but the source table has, the source table has more new data and should be inserted into the target table)

When notmatched by source-if the match is unsuccessful (there is no record in source but the target table has, the source table may have deleted the data, so target should also delete)

MERGE into @TargetTable as T           
USING @SourceTable as S in                
   t.id = s.id when                      
matched         
   THEN UPDATE SET t.dspt = S.DSPT when isn't  
matched by TARGET   
   THEN inserts VALUES (S.ID,S.DSPT) when not
matched by SOURCE            
   THEN delete< C9/>output $ACTION as [ACTION],
   deleted.id as ' Deleted ID ',
   deleted.dspt as ' Deleted Description ',
   Inserted.id as ' Inserted ID ',
   inserted.dspt as ' Inserted Description ' into
@Log;

Also note that there are some restrictions:

In the Merge matched operation, only the UPDATE or DELETE statement can be allowed to execute.

In the Merge not matched operation, only INSERT statements are allowed to execute.

The matched operation that occurs in a Merge statement can only occur once for an UPDATE or DELETE statement, or the following error occurs-an action of type ' when matched ' cannot appear more t Han once in a ' UPDATE ' clause of a MERGE statement.

The Merge statement must finally contain a semicolon to; End.

Execute the MERGE statement above to see the results, the data for the two tables is exactly the same-

The ID = 1,2,3 record exists in both the Source and target tables, so the UPDATE operation is performed.

The ID = 4,5 record exists in the Source table, but does not exist in the Target table, so an INSERT operation is performed.

The ID = 6,7 record exists in the Target table, but does not exist in the Source table and therefore performs a DELETE operation.