"Reprint" SQL Server-use the Merge statement to synchronize contrast between table data

Original address: SQL Server-Use the Merge statement to synchronize comparison between table data

There are many ways to implement synchronization between table data, such as deleting and then re-insert, or writing some other branch criteria to determine whether to insert or UPDATE. Included in the SSIS package is the ability to synchronize between table data through a combination of various tasks such as Lookup, Condition Split, and so on. "Synchronous" here means that each time a piece of code is executed, it ensures that the data of table A is always the same as the data in table B.

Can be implemented through the Merge statement 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 introduce some of its common features.

Test table-A Source table, a Target table, and a logging table to record the actions performed each time.

The following are the main synchronization operations

Merge into-the destination of the data, the table object where the data will eventually be merge

USING the condition associated with the source table connection on

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

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

When the 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 this data, so target should also be deleted)

MERGE into @TargetTable  asT USING@SourceTable  asS onT.id=s.id whenmatched Then UPDATE SETT.dspt=S.DSPT when  notMatched byTARGET Then INSERT VALUES(S.ID,S.DSPT) when  notMatched bySOURCE Then DELETEOUTPUT $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;

It is also important to note that there are some restrictions:

• In the Merge matched operation, only UPDATE or DELETE statements can be allowed to execute.
• In the Merge not matched operation, only INSERT statements are allowed.
• A matched operation that appears in a Merge statement can only occur once or DELETE statements, otherwise the following error will occur-an action of type ' when matched ' cannot appear more th An once in a ' UPDATE ' clause of a MERGE statement.
• The Merge statement must contain a semicolon at the end; End.

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

The record of the ID = A/A is present in both the Source table and the target table, so the UPDATE operation is performed.

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

The record for ID = 6,7 exists in the Target table, but does not exist in the Source table, so a DELETE operation is performed.

"Reprint" SQL Server-use the Merge statement to synchronize contrast between table data