Access - 合并两个结构相同的数据库 [英] Access - Merge two databases with identical structure

问题描述

我想编写一个将两个 Access 2000 数据库合并为一个的查询.每个都有 35 个具有相同字段和大部分唯一数据的表.有些行将具有相同的主键"，在这种情况下，数据库 A 中的行应始终优先于数据库 B.我在主键"周围使用引号，因为生成的数据库没有任何键或关系.例如:

I would like to write a query that merges two Access 2000 databases into one. Each has 35 tables with identical fields and mostly unique data. There are some rows which will have the same "primary key" in which case the row from database A should always take precedence over database B. I use quotes around "primary key" because the databases are generated without any keys or relationships. For example:

数据库 A，表 1

col1    col2
Frank   red
Debbie  blue

数据库 B，表 1

col1    col2
Harry   orange
Debbie  pink

我想要的结果:

col1    col2
Frank   red
Harry   orange
Debbie  blue

这些数据库是由不精通sql的用户生成和下载的，所以我只想给他们一个查询来复制和粘贴.他们显然必须首先将一个数据库 [in] 导入或链接到另一个.

These databases are generated and downloaded by non-sql-savvy users, so I would like to just give them a query to copy and paste. They will obviously have to start by importing or linking one DB [in]to another.

我猜我必须用组合结果查询创建第三个表，然后删除另外两个.不过，理想情况下，它只会将数据库 A 的行添加到数据库 B 中(必要时覆盖).

I'm guessing I will have to make a third table with the combined results query and then delete the other two. Ideally, though, it would just add database A's rows to database B's (overriding where necessary).

我当然不是在寻找完整的答案，只是希望得到一些关于从哪里开始的建议.我有一些 mySQL 经验并且了解连接的基础知识.是否可以在一个查询中完成所有这些操作，还是我必须为每个表分别创建一个?

I'm of course not looking for a complete answer, just hoping for some advice on where to start. I have some mySQL experience and understand the basics of joins. Is it possible to do this all in one query, or will I have to have a separate one for each table?

谢谢！！

推荐答案

怎么样:

SELECT t.ID, t.Field1, t.Field2 INTO NewTable FROM
(SELECT a.ID, a.Field1, a.Field2
FROM Table1 A
UNION
SELECT x.ID, x.Field1, x.Field2
FROM Table1 x IN 'C:\docs\db2.mdb'
WHERE x.ID NOT IN (SELECT ID From Table1)) t

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