database SQL Server - 同步 2 个不同数据库上的 2 个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13613983/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server - synchronizing 2 tables on 2 different databases
提问by GilliVilla
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 的同一台服务器上的 2 个不同数据库上有 2 个具有相同架构的表。一张表更频繁地更新数据。
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 ?
现在需要保持这两个表同步。这可以作为一个夜间过程发生。实现同步的最佳方法是什么。过程 ？
回答by Mathew A.
Using MERGE is your best bet. You can control each of the conditions. WHEN MATCHED THEN, WHEN UNMATCHED THEN etc.
使用 MERGE 是您最好的选择。您可以控制每个条件。当匹配时，当不匹配时等等。
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
回答by java4script
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让您将目标同步到源。
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脚本。
回答by Tys
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 有一个不错的新 MERGE 语句，您可以使用它在一个 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-tables-using-sql-server-integration- services-ssis-part-i-of-ii.aspx
