将SQL Server数据库整合为1个 [英] Consolidate SQL Server databases into 1

问题描述

我需要将20个具有相同结构的数据库合并为1个数据库.我看到了这篇文章: 将来自许多不同数据库的数据整合到一个数据库中延迟

I need to consolidate 20 databases that have the same structure into 1 database. I saw this post: Consolidate data from many different databases into one with minimum latency

我不了解所有这些内容，所以让我这样问:有些表具有主键，但是没有sourceID，例如:

I didn't understand all of this so let me ask like this: There are some table who have primary keys but don't have sourceID, example:

数据库1

AgencyID    Name 
1           Apple
2           Microsoft

数据库2

AgencyID   Name
1          HP
2          Microsoft

很明显，这两个表无法像这样合并，它需要附加列:

It's obvious that these two tables cannot be merged like this, it needs aditional column:

数据库1

Source     AgencyID    Name 
DB1        1           Apple
DB1        2           Microsoft

数据库2

Source     AgencyID   Name
DB2        1          HP
DB2        2          Microsoft

如果这是正确的方法，可以将这两个表合并到一个数据库中，如下所示:

If this is the right way of doing this, can these two tables be merged in one database like this:

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

...并且可以通过事务复制来做到这一点吗?

...and is it possible to do it with Transactional replication?

预先感谢您的回答，如果我能找到正确的答案，那将非常有帮助.

Thanks in advance for the answer, it would be really helpful if I would get the right answer for this.

Ilija

推荐答案

我解决了这个问题.现在，我正在使用事务复制.在"出版物属性>文章属性"中，我必须将"正在使用名称的操作"标志设置为"保持现有对象不变".默认值为"拖放现有对象并创建一个新对象". 在SQL 2008中，即使我更改表方案，这些更改也将应用于合并数据库.

I solved the problem. Now I am using Transactional Replication. In "Publication Properties > Article Properties" I have to set "Action if name is in use" flag to "Keep existing object unchanged". Default is "Drop existing object and create a new one". In SQL 2008 even when I change table scheme these changes are applied to consolidation database.

这篇关于将SQL Server数据库整合为1个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！