SQL Server - synchronizing 2 tables on 2 different databases

Question

I have 2 tables with same schema on 2 different databases on the same server with SQL Server 2008 R2. One table gets updated with data more often.

Now there is a need to keep these 2 table in sync. This can happen as a nightly process. What is the best methodology to achieve the sync. process ?

Answer 1

You probably can use sql server's tablediff.exe command line utility. It can do table-by-table, one-off compare between two tables and generate the sql automatically for you to sync the dest to the source.

There's also a GUI wrapper around it http://code.google.com/p/sqltablediff/ which makes the job even easier. It will generate the command line for you.

You can then create a scheduled task to run the command line, and then execute the generated sql scripts.

Answer 2

Using MERGE is your best bet. You can control each of the conditions. WHEN MATCHED THEN, WHEN UNMATCHED THEN etc.

MERGE - Technet

MERGE- MSDN (GOOD!)

Example A: Transactional usage - Table Variables - NO

DECLARE @Source TABLE (ID INT)
DECLARE @Target TABLE (ID INT)

INSERT INTO @Source (ID) VALUES (1),(2),(3),(4),(5)

BEGIN TRANSACTION

MERGE @Target AS T
USING @Source AS S
ON (S.ID = T.ID)
WHEN NOT MATCHED THEN
    INSERT (ID) VALUES (S.ID);

ROLLBACK TRANSACTION
SELECT  'FAIL' AS Test,*
FROM    @Target

Example B: Transactional usage - Physical Tables

CREATE TABLE SRC (ID INT);
CREATE TABLE TRG (ID INT);

INSERT INTO SRC (ID) VALUES (1),(2),(3),(4),(5)

BEGIN TRANSACTION

MERGE TRG AS T
USING SRC AS S
ON (S.ID = T.ID)
WHEN NOT MATCHED THEN
    INSERT (ID) VALUES (S.ID);

ROLLBACK TRANSACTION
SELECT  'FAIL' AS Test,*
FROM    TRG

Example C: Transactional usage - Tempdb (local & global)

CREATE TABLE #SRC (ID INT);
CREATE TABLE #TRG (ID INT);

INSERT INTO #SRC (ID) VALUES (1),(2),(3),(4),(5)

BEGIN TRANSACTION

MERGE #TRG AS T
USING #SRC AS S
ON (S.ID = T.ID)
WHEN NOT MATCHED THEN
    INSERT (ID) VALUES (S.ID);

ROLLBACK TRANSACTION
SELECT  'FAIL' AS Test,*
FROM    #TRG