19

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 ?

GilliVilla
  • 4,998
  • 11
  • 55
  • 96

3 Answers3

19

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
Matt
  • 1,441
  • 1
  • 15
  • 29
  • If you use physical tables yes... If you are using in memory ( @ variable tables) then no. I've edited the answer to include a quick demo of what you're asking... – Matt May 05 '16 at 17:23
  • Keep in mind that MERGE is an atomic statement (which is what you want) but has implicit transaction requirements, MSDTC may be the downfall if you are going across servers. Query plans can also be an issue at times. It is your best option, but has some challenges at times – Adriaan Davel Jun 28 '16 at 12:21
  • How I can do this action automatically after each change in source table? – Fred Sep 02 '17 at 19:59
  • @Fred you may use Change Tracking feature for that. – Turker Tunali Oct 17 '18 at 09:45
  • Does the 2 tables have to have the same primary key foreign key etc? or will this still work as long as the column name/type is the same? – Lightsout Mar 05 '21 at 01:50
7

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.

java4script
  • 547
  • 6
  • 5
  • I like this solution, Wouldn't trust it running in the background but very useful for a nightly report :-) – Liam Wheldon Feb 10 '16 at 10:48
  • Second this solution... We found that running tablediff.exe through a bat script called by SQL Server Agent did exactly what we needed. – Sean Halls Jul 19 '16 at 15:58
1

You can select from the different databases and use a cursor to loop the selected data. Within that cursor you can do some logic and update or delete from the target table.

Also SQL 2008 has a nice new MERGE statement which you can use to select/insert/update in one T-SQL query. http://technet.microsoft.com/en-us/library/bb510625%28v=sql.105%29.aspx

For more complex processes i use the first option. For more straight forward sync tasks i use the second option.

As an extra option there is also Server Integration Services (SSIS): http://blogs.msdn.com/b/jorgepc/archive/2010/12/07/synchronize-two-tables-using-sql-server-integration-services-ssis-part-i-of-ii.aspx

Tys
  • 3,592
  • 9
  • 49
  • 71