Importing data from database where tables are related - PK autonumber

[jaworski_m]

Hello,
I would like to import data from two tables, which are 1-many related, from database.A to database.B.
Primary Key is an autonumber field.


Is there a way to do it to keep referential integrity between the tables?

Thank you for hints.

[June7]

Do you want all the records or is this a selective import?

Why is this necessary? This will be a repetitive procedure, meaning you have to do this on a regular basis?

[jaworski_m]

Do you want all the records or is this a selective import?

All records are to be imported.

Why is this necessary? This will be a repetitive procedure, meaning you have to do this on a regular basis?

I want to combine a few (~8) databases of the same structure into one.
This is a task that will be done only once, so I can do it manually. My only concern is the autonumber field used for PK.

[June7]

Can be done.

Do the 1-side tables in each of the dbs currently use autonumber PK?

Import the records so that the original PK/FK data are saved into a regular number type field in both tables. The records of the 1-side will receive new PK. After each import, populate a field in each table with a source identifier (A, B, C, D, E, F, G, H) for that set of new records. Build a query that joins the tables on the old PK/FK values and the source identifier and update the new PK into another field of the many-side table.

Once everything looks good, can delete the old PK/FK fields as well as the Source fields.

[jaworski_m]

Works.
Thank you.

Small remark. During this operation "Enforce referencial integrity" must be (at least temporary) disabled, because it is not possible to append records to the table on many side if there is no PK the appended records can refere to.