将SQL Server数据库合并为1个

Question

我需要将结构相同的20个数据库合并成一个数据库。我看到了这篇文章：Consolidate data from many different databases into one with minimum latency 我没有完全理解这篇文章，所以我这样问：有一些表格有主键但没有sourceID，例如：

数据库1

AgencyID    Name 
1           Apple
2           Microsoft

数据库2

AgencyID   Name
1          HP
2          Microsoft

很明显，这两个表格不能像这样合并，需要添加额外的列：

数据库1

Source     AgencyID    Name 
DB1        1           Apple
DB1        2           Microsoft

数据库 2

Source     AgencyID   Name
DB2        1          HP
DB2        2          Microsoft

如果这是正确的做法，那么这两个表格可以像这样合并到一个数据库中吗：

Source     AgencyID    Name 
DB1        1           Apple
DB1        2           Microsoft
DB2        1           HP
DB2        2           Microsoft

...并且是否可以使用事务性复制来实现？

非常感谢您的答案，如果我能得到正确的答案，那将非常有帮助。

Ilija

- ilija veselica

---

这是一个一次性的合并过程吗？ - John Sansom

是的，我想将数据库合并成一个。 - ilija veselica

@ile - 他在询问这是否是一次性的过程，还是会定期重复进行？ - JNK

它将定期重复出现。 - ilija veselica

你想要达成什么目标（应用程序整合，报告，数据仓库）？ 在此过程结束后，这20个数据库还会被使用吗？此外，每个数据库的大致大小和表的数量是多少？ - Jeremy Gray

我正在尝试将20个数据库合并成一个，然后可能将此数据库用于数据仓库。每个数据库的大约大小为5GB...其中包含约30个表。我想合并所有数据以便在一个地方拥有全部数据。 - ilija veselica

Answer 1

如果我理解你的意思正确，你可以通过以下方式进行操作：

创建一个DTS/SSIS包。这里有一个基本的SSIS教程链接：basic SSIS tutorial。

或者直接运行SQL。

 INSERT INTO [TargetDatabase].dbo.[MergedAgency]([Source], [AgencyID], [Name])
 SELECT CAST('DB1' AS nvarchar(16)), [AgencyID], [Name]
 FROM [SourceDatabase1].dbo.[Agency]

 INSERT INTO [TargetDatabase].dbo.[MergedAgency]([Source], [AgencyID], [Name])
 SELECT CAST('DB2' AS nvarchar(16)), [AgencyID], [Name]
 FROM [SourceDatabase2].dbo.[Agency]

然后通过具有一个作业步骤和计划的重复的SQL Server Job 之一进行调用。

不要忘记考虑如何检测已经复制到目标数据库的哪些行。

- oleschri

---

这对于大型数据库来说似乎非常复杂... 我实际上使用事务复制（Transactional replication）在一个小而简单的数据库上完成了这个任务。第一次遇到问题是我没有将“如果名称已存在则执行操作”标志设置为“保留现有对象不变”。默认情况下是“删除现有对象并创建新对象”。 - ilija veselica

@ile（一如既往）这取决于情况。如果您想完全控制合并过程，可以在SSIS中编写代码。但是，如果事务复制现在甚至可以处理模式更改，并且您有许多要合并的表格，我可以看到它的优点。 - oleschri

Answer 2

我解决了这个问题。现在我正在使用 事务性复制。在“发布属性 > 文章属性”中，我必须将“名称已在使用时的操作”标志设置为“保留现有对象不变”。默认值为“删除现有对象并创建一个新对象”。 在 SQL 2008 中，即使我更改表方案，这些更改也会应用于合并数据库。

- ilija veselica

---

似乎SQL Server的事务复制在2008版本中比SQL Server 2000更加灵活。很期待听到您长期运行的效果如何。 - oleschri

它还没有进入生产，但如果我遇到新问题，我会通知您。 - ilija veselica

Answer 3

SQL-Hub (http://sql-hub.com) 可以让您将多个具有相同模式的数据库合并成一个单独的数据库。虽然您可能需要支付许可证才能安排自动运行过程，但有一个免费的许可证可以让您从 UI 中执行此操作。它比复制更容易使用 - 虽然效率不如复制高。

- Alan Hickman