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 take database B's tables and add in database A'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?

Answer 1

I'm not familiar with access, but in generic SQL I would do it this way:

SELECT col1, col2 from TableA
UNION
SELECT col1, col2 from TableB where col1 not in (select col1 from TableA)

This will give preference to Debbie in TableA. Basically, you are joining the whole two tables one after the other using UNION, but just before adding TableB in you are stripping it from whatever content in col1 already exists in TableA.

EDIT: I'm only talking about joining 2 tables, not two databases. But you can replicate the idea for each table as long as there are no colliding relationships.

EDIT2: If you prefer to modify TableA directly, you can use INSERT (note you can't retrieve the original TableA this way unless you add some extra info in a new column to keep track of your operation)

INSERT INTO TableA (col1, col2) 
SELECT col1, col2 from TableB 
WHERE col1 not in (select col1 from TableA)

Answer 2

Unfortunately this can't be done with a single query.

There are a few good database merge tools that will help:

• Altova DatabaseSpy
• RedGate SQL Data Compare

Obviously licensing for a commercial tool will present a problem if your intention is to give something to your clients to use.

Writing a tool yourself is always an option but database merging isn't exactly a trivial operation. You can take a look at this post over at stackoverflow, which discusses some of the problems you're likely to face. You may have an even worse time of it because your database isn't using referential integrity checking. You might also look into ETL Tools. Not exactly designed for a merge scenario but you might find something that will do the job.

Answer 3

Because of foreign keys, you need to keep track of the old PKs in the combined table. I always create an OldID column in that case, and append the parent records to the table with the original PK appended to the OldID field. Then I can use that OldID to link to the child records and append them with the new PK value.