Hello all, I've got a requirement to import specific records from a series of cascading tables from one database to another.
certTable has a unique column (ticketNum) containing ticket numbers, with a PK of ID
deviceTable has is related to certTable via a 1:M relationship based on the ticketNum column of both tables, with a PK of ID
deviceStorageTable is related to deviceTable via a 1:M relationship based on the deviceTable.ID column
There are TWO databases (or sets of linked tables in a single Access file). One source and one target.
The source tables are called stg_certTable, stg_deviceTable and stg_deviceStorageTable. All three sets of tables have different auto increment values on the ID fields of each table.
Importing records for which the record contains the ticketNum is easy, and working fine. However, I need to import all associated records from stage (source) to the target structure - including records in the deviceStorageTable which does not have the ticketNum field and even if it did, the table is related via the ID field which is different on each set of tables.
Question: How would I go about importing records for the deviceStorageTable while changing the deviceID value for the corresponding record to the NEW ID the deviceTable?
example:
stg_certTable.TicketNum = "Ticket123"
stg_deviceTable.TicketNum = "Ticket123", stg_deviceTable.ID = 1
stg_deviceStorageTable.deviceID = 1
Because the ID fields are set when the records are created via the auto increment function, we get results such as:
certTable.TicketNum = "Ticket123"
deviceTable.TicketNum = "Ticket123", stg_deviceTable.ID = 37
deviceStorageTable.deviceID = 1
and the record in deviceStorageTable is no longer associated with the proper record in deviceTable.
I need to be able to run through something like a foreach loop at the time for each deviceTable record and again for each deviceStorageRecord, but do not know the best way to do that.
Logic example:
Code:
INSERT INTO certTable from stg_CertTable where TicketNum = "Ticket123"
FOREACH RECORD IN stg_deviceTable where TicketNum = "Ticket123"INSERT INTO deviceTable from stg_deviceTable where TicketNum="Ticket123"
var_newDeviceID = deviceTable.ID
var_oldDeviceID = stg_deviceTable.ID
FOREACH RECORD IN stg_deviceStorageTable WHERE stg_deviceStorageTable = var_oldDeviceID
INSERT INTO deviceStorageTable from stg_deviceStorageTable (replacing deviceID with var_newDeviceID)
Or something like that.
Any help would be greatly appreciated - thanks...