如何在SQL Server中合并两个数据库？ [英] How to merge two databases in SQL Server?

问题描述

这两个数据库具有相同的模式，但在一些表中它们可能会遇到与主键的冲突。所以我希望他们只是忽略重复的行，并继续进一步合并。

Both databases have the same schema, but they may experience conflict with primary key in some tables. So I want them to just ignore the duplicate rows, and continue merging further.

推荐答案

你目前使用的是一个差的。

First a conflict of keys indicates that whatever process you are currently using is a poor one.

要正确合并使用自动生成（非GUID）键的两个数据库，您需要执行几个步骤。首先向父表添加一个新的自动生成的密钥，然后从两个表导入所有数据，将旧的旧文件重命名为ID_old，并将新文件重命名为旧的ID名。此时，您可以在子表上移动。您将需要通过加入父表并将新的id字段作为外键的值而不是现有表中的值来复制到子表。您将需要为每个外键表重复此过程，如果该表也是父表，您需要在复制任何数据之前将conversionid字段添加到表中，以便您可以在链中工作。要做到这一点，正确地涉及大量的数据库结构和大量的规划的知识。如果没有两个源数据库的良好备份，请不要考虑这样做。如果两个数据库都在单用户模式下，也可以进行这个过程。

To correctly merge two database which are using autogenerated (non_GUID) keys, you need to take several steps. First add a new autogenerated key to the parent table, then import all the data from both tables, rename the old the old is file to ID_old and rename the new files to the old id name. At this point you can then move onthe the child tables. You will need to copy to child tables by joining to the parent table and taking the new id field as the value for the foreign key instead of the one in the existing table. You will need to repeat this process for every foreign key table and if that table is also a parent table, you will need to add the conversionid field to the table before copying any data, so that you you can work all the way down the chain. To do this properly involves a great deal of of knowlege of the structure of the database and lots of planning. Do not consider doing this without a good backup of both source databases. It is also best if the process can happen when both dabases are in single user mode.

如果你使用自然键，并且有重复，你有一个完全不同的问题。所有重复的键记录首先被移动到单独的表，并且关于哪个是更正确的数据的确定。在某些情况下，你会发现自然键实际上并不是唯一的（他们很少是这就是为什么我几乎没有使用它们），合并的数据库将需要使用某种类型的自动生成的键。这将涉及代码更改以及数据库更改，因此它是最后的选择。

If you use natural keys and have duplicates, you have a far different problem. All duplicate key records whould be moved to a separate table first and a detemination as to which is the more correct data should be made. In some cases you will find that the natural key is in fact not unique (they rarely are which is why I almost nver use them) and the merged database will need to work with an autogenerated key of some type. This will involve code changes as well as database changes, so it is the option of last resort.

使用自然键找到的数据通常是不同的，但是类似的（地址中的圣西弗街）在这种情况下标记其中一个记录for insert，然后在两个步骤中插入时，首先是没有重复的记录，然后是在duplicatees表中被标记为插入的记录。记住你将必须检查所有外键表中的所有记录，以确定要保留哪些不保留。只是抛出任何重复是一个坏主意，你会失去数据的方式，可能是关键数据（如客户的订单）。这是一个冗长乏味的过程，需要有数据专业知识的人做出决定。作为一个程序员，你应该为他们提供一个重复数据删除工具，让他们检查每一套重复的所有数据，选择保存什么，删除什么，然后标记出来，它将运行一个过程插入记录。 Remeber在你的设计中，对于真正的重复，将有一些子表（例如订单）需要记录从发送到数据库的记录选择作为一个输入（订单是一个例子），对于其他表你会想要选择哪个是正确的（例如地址）。所以你可以看到这是一个复杂的过程，需要对数据库进行彻底的了解。

What you find often with natural keys is that the data for each one is different but simliar (St. vice Street in the address) in this case mark one of the records for insert and then when do the insert in two steps, first the records which have no duplicates, then the records in the duplicates table that are marked for insertion. Remember you will have to examine all records in all foreign key tables to make the determination which to keep and which not to keep. Just throwing out any duplicates is a bad idea and you will lose data that way, possibly critical data (such as a customer's orders). This is a long tedious process which will require someone with expertise in the data to make the determinations. As a programmer, you should provide them a dedup tool that will let them examine all the data for each set of duplicates and choose what to keep and what to get rid of and then having marked everyithing, it will run a process to insert the records. Remeber in your design, that for true duplicates, there will be some child tables (such as orders ) that need the records from both sent to the database for the record chosen as the one to enter (orders is an example), for other tables you will want to choose which is correct (address for instance). So you can see this is a complex process requiring a thorough understanding othe database.

如果你有很多重复，他们可能正在清理和添加数据几个月，所以一个工具真的很关键。这样做的人可能是系统用户，而不是数据库专家或程序员，因为他们是唯一真正能够对哪个记录保持最佳判断的人。可能你需要在任何事件中做类似的事情，因为可能有记录是重复的，即使你有一个自动生成的密钥。它们只是更难找到。

If you have a lot of duplicates, they may be cleaning up and adding the data for several months, so a tool is really critical. The people doing this will likely be system users not database specialists or programmers as they are the only people who truly can make the judgement most of the itme as to which record to keep. Likely you will need to do something simliar in any event as there may be records which are duplicates even when you have an auto-generated key. They are just more difficult to find.

没有简单的方法来合并两个数据库（即使使用GUIDS，你有自然键中重复的问题）。

There is no easy way to merge two databases (even using GUIDS, you have the problem of duplicates in the natural key).

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