如何将两个相同的数据库数据合并为一个? [英] How to merge two identical database data to one?

问题描述

两个客户将要合并.他们都在使用我的应用程序，以及他们自己的数据库.大约几周后，他们将合并(成为一个组织).所以他们希望将所有数据都放在 1 个数据库中.

Two customers are going to merge. They are both using my application, with their own database. About a few weeks they are merging (they become one organisation). So they want to have all the data in 1 database.

所以这两个数据库结构是相同的.问题出在数据上.例如，我有表位置和人员(这些只是两个 50 的表):

So the two database structures are identical. The problem is with the data. For example, I have Table Locations and persons (these are just two tables of 50):

数据库 1:

位置:

Id    Name         Adress   etc....
1     Location 1
2     Location 2

人员:

Id    LocationId     Name     etc...
1     1              Alex
2     1              Peter
3     2              Lisa

数据库 2:

位置:

Id    Name         Adress   etc....
1     Location A
2     Location B

人员:

Id    LocationId     Name     etc...
1     1              Mark
2     2              Ashley
3     1              Ben

我们看到那个人与位置有关(列locationId).请注意，我有更多的表是指位置表和人员表.

We see that person is related to location (column locationId). Note that I have more tables that is referring to the location table and persons table.

数据库包含它们自己的位置和人员，但 Id 可以相同.以防万一，当我想将所有内容导入 DB2 时，应该将 DB1 的位置插入到 ID 为 3 和 4 的 DB2 中. DB1 中的人员应该有新的 Id 4、5、6，人员表中的位置也必须更改为 ids 4,5,6.

The databases contains their own locations and persons, but the Id's can be the same. In case, when I want to import everything to DB2 then the locations of DB1 should be inserted to DB2 with the ids 3 and 4. The the persons from DB1 should have new Id 4,5,6 and the locations in the person table also has to be changed to the ids 4,5,6.

我对这个问题的解决方案是编写一个处理所有内容的查询，但我不知道从哪里开始.

My solution for this problem is to write a query which handle everything, but I don't know where to begin.

(在查询中)对 Id 字段重新编号的最佳方法是什么?数据库不包含参照完整性和外键(外键未在数据库中定义).创建 FKey 和级联不是一种选择.

What is the best way (in a query) to renumber the Id fields also having a cascade to the childs? The databases does not containing referential integrity and foreign keys (foreign keys are NOT defined in the database). Creating FKeys and Cascading is not an option.

我使用的是 sql server 2005.

I'm using sql server 2005.

推荐答案

你说两个客户都在使用你的应用程序，所以我假设它是某种收缩包装"软件，被更多客户使用，而不仅仅是这些二，对吗?

You say that both customers are using your application, so I assume that it's some kind of "shrink-wrap" software that is used by more customers than just these two, correct?

如果是的话，向表中添加特殊列或类似的东西可能会在未来造成痛苦，因为您要么必须为这两个客户维护一个可以处理额外列的特殊版本.或者，您必须将这些列引入您的主代码库，这意味着您的所有其他客户也会获得它们.

If yes, adding special columns to the tables or anything like this probably will cause pain in the future, because you either would have to maintain a special version for these two customers that can deal with the additional columns. Or you would have to introduce these columns to your main codebase, which means that all your other customers would get them as well.

我可以想到一种更简单的方法来做到这一点，而无需更改您的任何表格或添加任何列.
为了让这个工作，你需要找出存在于两个数据库中的最大 ID(不管它在哪个表或哪个数据库中).

I can think of an easier way to do this without changing any of your tables or adding any columns.
In order for this to work, you need to find out the largest ID that exists in both databases together (no matter in which table or in which database it is).

这可能需要一些副本&粘贴以获得许多如下所示的查询:

This may require some copy & paste to get a lot of queries that look like this:

select max(id) as maxlocationid from locations
select max(id) as maxpersonid from persons
-- and so on... (one query for each table)

当您在两个数据库中运行查询后找到最大的 ID 时，取一个大于该 ID 的数字，并将其添加到第二个数据库中所有表的所有 ID 中.
非常重要的是，该数字需要大于两个数据库中已经存在的最大 ID！
解释起来有点困难，举个例子:

When you find the largest ID after running the query in both databases, take a number that's larger than that ID, and add it to all IDs in all tables in the second database.
It's very important that the number needs to be larger than the largest ID that already exists in both databases!
It's a bit difficult to explain, so here's an example:

假设两个数据库中任何表中的最大 ID 为 8000.
然后运行一些 SQL，将 10000 添加到第二个数据库中每个表中的每个 ID:

Let's say that the largest ID in any table in both databases is 8000.
Then you run some SQL that adds 10000 to every ID in every table in the second database:

update Locations set Id = Id + 10000
update Persons set Id = Id + 10000, LocationId = LocationId + 10000
-- and so on, for each table

查询相对简单，但这是最多的工作，因为您必须为数据库中的每个表手动构建这样的查询，并使用所有 ID 列的正确名称.

The queries are relatively simple, but this is the most work because you have to build a query like this manually for each table in the database, with the correct names of all the ID columns.

在第二个数据库上运行查询后，您问题中的示例数据将如下所示:

After running the query on the second database, the example data from your question will look like this:

数据库 1:(和以前完全一样)

位置:

Id    Name         Adress   etc....
1     Location 1
2     Location 2

人员:

Id    LocationId     Name     etc...
1     1              Alex
2     1              Peter
3     2              Lisa

数据库 2:

位置:

Id    Name         Adress   etc....
10001 Location A
10002 Location B

人员:

Id    LocationId     Name     etc...
10001 10001          Mark
10002 10002          Ashley
10003 10001          Ben

就是这样！现在，您可以将数据从一个数据库导入到另一个数据库中，而不会出现任何主键冲突.

And that's it! Now you can import the data from one database into the other, without getting any primary key violations at all.

这篇关于如何将两个相同的数据库数据合并为一个?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！