SQL Server-同步2个不同数据库上的2个表[英] SQL Server - synchronizing 2 tables on 2 different databases

本文是小编为大家收集整理的关于SQL Server-同步2个不同数据库上的2个表的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我在同一服务器上的2个不同数据库上使用SQL Server 2008 R2有2个具有相同架构的表. 一张表更新数据.

现在需要将这2个表保持在同步中.这可以作为每晚的过程发生. 什么是实现同步的最佳方法.流程?

推荐答案

使用合并是最好的选择.您可以控制每个条件.当匹配时,当无与伦比的时候等等.

Merge -Technet -Technet

merge-merge-msdn(good!)(好!)

示例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

示例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

示例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

其他推荐答案

您可能可以使用SQL Server的TableDiff.exe命令行实用程序.它可以按表进行桌面,一次性比较两个表之间,并自动生成SQL,以使您将dETS同步到源.

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

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

其他推荐答案

您可以从不同的数据库中进行选择,并使用光标循环选定的数据.在该光标中,您可以从目标表中执行一些逻辑并更新或删除.

另外,SQL 2008具有一个不错的新合并语句,您可以在一个T-SQL查询中选择/插入/更新. http://technet.microsoft.com/en -us/library/bb510625%28V = SQL.105%29.aspx

对于更复杂的过程,我使用第一个选项.对于更直接的同步任务,我使用第二个选项.

作为额外的选项,还有服务器集成服务(SSIS): http://blogs.msdn.com/b/jorgepc/archive/2010/12/12/07/synchronize-two-tables-using-sql-server-integration-server-integration-services-services-services-services-services-services-services-services-services-services-services-services-services-services-services-sersis -part-i-of-ii.aspx

本文地址:https://www.itbaoku.cn/post/597679.html

问题描述

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 ?

推荐答案

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

其他推荐答案

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.

其他推荐答案

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