CODEWITHSUNDEEP
sqlsql-servermerge
devHead
devHead

Reputation: 822

Synchronizing 2 tables with MERGE

I’ve been tasked to synchronize 2 tables (both are identical). They have 60 columns each. Table A is the primary table that will be initially filled. I need to create a stored procedure (done) that will merge these 2 tables and populate both with the same exact data (Update, insert, delete) when called. How would I use the MERGE function in SQL to achieve this? I’ve looked at both the MSDN documentation and similar that’s on technet, but I’m pretty confused on getting started. Do I need to specify each field I need merged? Or is it a simple call I’m missing that will perform this action?

Upvotes: 2

Views: 6462

Answers (2)

AlexPalla
AlexPalla

Reputation: 301

Over 60 columns is a great number! When I need to sync 2 identical table I do:

;WITH tbl_to_synch as (
    -- Prepare table to update,
    Select *,chk = CHECKSUM(*) from [dbo].[tableA]
)
MERGE tbl_to_synch as [Target]
USING (Select *,chk = CHECKSUM(*) from [dbo].[tableB]) as [source]
ON [Target].key = [source].key
WHEN MATCHED AND [Target].chk <> [source].chk THEN 
-- UPDATE ONLY row that is changed
UPDATE
    SET 
        column01 = [source].[column01]
        ,column02 = [source].[column01]
        -- .... 
        ,column59 = [source].[column59]
        ,column60 = [source].[column59]

WHEN NOT MATCHED BY TARGET THEN
    insert (column01, column02, ...,column59,column60)
    values (column01, column02, ...,column59,column60)
WHEN NOT MATCHED BY SOURCE THEN DELETE
-- Show what is changed
OUTPUT $action, ISNULL(INSERTED.key,DELETED.key);

Upvotes: 6

twoleggedhorse
twoleggedhorse

Reputation: 5048

Here is a link to a simple example of the MERGE statement:

http://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/

The basic syntax reads as:

MERGE table1
USING table2
ON table1.id = table2.id
WHEN MATCHED THEN
--Do an update here
WHEN NOT MATCHED BY TARGET THEN
--Do an insert here (or a delete)
;

You can also use WHEN NOT MATCHED BY SOURCE

Upvotes: 6

Related Questions