Access - Merge two databases with identical structure

Access - Merge two databases with identical structure

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:

Asked by: Guest | Views: 276

Total answers/comments: 2

Comments display order:

Guest [Entry]

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

Guest [Entry]

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.