Synchronizing 2 tables with MERGE

Question

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?

Correct, i want to syncronize both tables... from my research it looks like MERGE is what i want to use. I could be wrong, i just Haven’t seen any examples with tables that contain over 60 columns. I just overall confuse where to begin... I'm a C# developer getting out of my comfort zone... — devHead, Feb 13 '13 at 18:01

score 6 · Accepted Answer · answered Feb 13 '13 at 17:57

6

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

answered Feb 13 '13 at 17:57

twoleggedhorse

4,938
4
23
38

This helped complete my task! not only helped but reduced sync time by over 1 hour vs. last method i was using!! – devHead Feb 14 '13 at 15:49
@twoleggedhorse can you pls tell me is this merge statement technique is enough for a big database which is constantly changing and we need to synchronise that – Dragon Sep 07 '17 at 18:29
@twoleggedhorse or this has some drawbacks for big databases and is there any other better solution available? – Dragon Sep 07 '17 at 18:30
@twoleggedhorse also the database should be synched every second and that will be two way means both the databases should be matched – Dragon Sep 07 '17 at 18:31

score 6 · Answer 2 · answered May 16 '17 at 10:32

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);

Love the use of the CTE to define the target table. Can get a huge speed improvement on MERGE statements that have WHEN NOT MATCHED BY SOURCE. For more info see my answer on https://stackoverflow.com/questions/7626719/sql-server-delete-and-merge-performance/41295187#41295187 — David Coster, Jul 14 '17 at 05:27
This is the answer I was looking for! I like checking the hashes instead of each individual value. Perfect — spicy.dll, Aug 06 '18 at 18:18

Synchronizing 2 tables with MERGE

2 Answers2