Access - Merge two databases with identical structure

Question

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:

Database A, table1

col1    col2
Frank   red
Debbie  blue

Database B, table1

col1    col2
Harry   orange
Debbie  pink

And the results I would like:

col1    col2
Frank   red
Harry   orange
Debbie  blue

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.

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).

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?

THANKS!!

Answer 1

This isn't an SQL solution, but may work just as well as telling your non-sql savvy users to cut and paste SQL statements.

1. I suggest defining a unique Key on the table that takes precedence (col1).
2. Then copy all the data from Database B into the 'master' table.

This will fail for all duplicates, but insert any 'new' records. Remove the unique key constraint after you're done if necessary.

From your question it looks like you need the resulting table in database B. So you may want to have your users copy the table into database B before you start or after you're done.

Answer 2

How about:

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