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

Source: Internet
Author: User
Tags insert

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.