如何合并两个数据库，数据相同，但PK不同，没有重复的字段？

本文是小编为大家收集整理的关于如何合并两个数据库，数据相同，但PK不同，没有重复的字段？的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。

中文
English

问题描述

我有两个MDB文件. 如有必要，我也可以将其转换为MySQL 数据库.

如何将这两个不同的DB合并到一个单个DB?

这个想法是在不复制任何客户端的情况下将所有信息表格既均已DB并合并为一个. 问题在于两个BD都有相同的客户端和不同的客户端，但是客户的PK在他们身上并不相同.

每行都有一个独特的字段，我想它可以以某种方式帮助. 我该怎么做的任何想法?

其他推荐答案

我将运行一个更新，以填充一个带有所有信息的表.

假设第一表具有第二个表具有的所有名称(表2中没有名称值在表1中没有)您应该能够运行以下更新以使第一个表完整:

update tclient1 t join (select name,
                               max(tel) as tel_filled,
                               max(address) as add_filled
                          from (select name, tel, address
                                  from tclient1
                                union all
                                select name, tel, address
                                  from tclient2) x
                         group by name) x on t.name = x.name
   set t.tel = x.tel_filled and t.address = x.add_filled;

请参阅小提琴:其他推荐答案

禁用外国钥匙(请参阅在这里)
在第二个db中更新fk，因此使其独一无二，例如:

更新客户端设置id_client = id_client + 1000000;

更新历史记录设置ID_CLIENT = ID_CLIENT + 1000000， id_history = id_history + 10000000;
使FKS可以检查诚信
将第二个db导出为sql-inserts，并在第1 dB中执行.

请使用备份.

本文地址：https://www.itbaoku.cn/post/597340.html

百度翻译此文有道翻译此文

问题描述

I have two mdb files. I can also convert it to MySQL database, if necessary.

How can I merge these two different dbs to a single one?

The idea is to get all info form both dbs and merge into one, without duplicating any client. The problem is that both bds have the same clients, and different ones, but the PKs of the clients aren't the same on them.

Every line has a unique field, I guess it can help somehow. Any idea of how can I do that?

其他推荐答案

I would run an UPDATE to populate one of the tables w/ all info available.

Assuming the first table has all names that the second table has (that there are no name values in table 2 that are not in table 1) you should be able to run the following update to make the first table complete:

update tclient1 t join (select name,
                               max(tel) as tel_filled,
                               max(address) as add_filled
                          from (select name, tel, address
                                  from tclient1
                                union all
                                select name, tel, address
                                  from tclient2) x
                         group by name) x on t.name = x.name
   set t.tel = x.tel_filled and t.address = x.add_filled;

See fiddle: http://sqlfiddle.com/#!2/3e7dc/1/0

其他推荐答案

Disable foreign keys (see here)
Update FK in the 2nd DB so make them unique, for instance:

update Client set id_client = id_client + 100000000;

update History set id_client = id_client + 100000000, id_history = id_history + 10000000;
Enable FKs to check integrity
Export 2nd DB as SQL-inserts and execute it in the 1st DB.

Use backups, please.

上一篇：围绕SQL查询的中期解决方案

下一篇：如何将结果集复制到对象中？