SQL Server——在两个不同的数据库上同步两个表

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.

我在同一个服务器上有两个具有相同模式的表，使用SQL server 2008 R2。一个表可以更频繁地更新数据。

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 ?

现在需要保持这两个表的同步。这可能是一个夜间过程。实现同步的最佳方法是什么?过程?

3 个解决方案

#1

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

合并——MSDN(好!)

Example A: Transactional usage - Table Variables - NO

示例A:事务使用—表变量—没有

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

示例B:事务性使用-物理表。

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)

示例C:事务使用—Tempdb(本地和全局)

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

#2

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.

您可能可以使用sql server的tablediff。exe命令行实用程序。它可以逐个表地对两个表进行一次性比较，并自动生成sql，以便您将dest同步到源代码。

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.

它周围还有一个GUI包装器http://code.google.com/p/sqltablediff/，这使得工作更加容易。它将为您生成命令行。

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

然后，您可以创建一个计划任务来运行命令行，然后执行生成的sql脚本。

#3

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

SQL 2008还有一个很好的新的合并语句，您可以使用它在一个T-SQL查询中选择/插入/更新。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

作为一个额外的选项，也有服务器集成服务(SSIS): http://blogs.msdn.com/b/jorgepc/archive/2010/12/07/synchronize-two-table -service -sql- Server - integrationservices - SSIS -part- ii.aspx。

#1