How to duplicate cascaded records (1 Viewer)

[jan@BRU]

Hello
I have a database, which has the following structure:

Table 1 (Files) (ID1, Filename)
Table 2 (Schedules) (ID2, Airline, ...., ID1)
Table 3 (Flights) (ID3, Flightnumber, ...., ID2)
Table 4 (Additionalinfo) (ID4, Soandso, ...., ID3)

As you can see it's a real non-redundant structure, where unique identifiers ID1, ID2, ID3, ID4 are cascading into each other.

Now here comes the tricky question, which seems obvious, but i can't get it to work.

I would like to be able to copy (i.e. duplicate) the information in this database, for any given level 1-3. Let's say I want to select one flight (with it's oen record in tble 3 and all associated records in Table 4) and go ahead and copy and paste the relevant information in Tables 3 and 4 in such a way, that the new record is Table 3 is identical to the old record in table 3 (albeit for the unique identifier ID3) and the new records in Table 4 are identical to the old records in Table 4, albeit for the new unique identifiers ID4 and the link ID3 (which should no longer relate to the original copy of ID3, but to the just now created new record).

Seems straightforward, and i can get it to work, the following way:

append query in Table 3, lookup the last created ID3, append query in Table 4.

so far so good

The problem is, i also want to be able to do all of that also one and two levels up: i.e. Take a file in Table 1 and copy this record, plus all cascadingly associated records in Tables 2, 3 and 4.

theoretically, i can apply the above:

Append query in Table 1, lookup the last created ID1.
Loop through all the linked records in Table 2. For each Table 2 record:
Append query in Table 2, lookup the last created ID2.
Loop through all the linked records in Table 3. For each Table 3 record:
Append query in Table 3, lookup the last created ID3.
Loop through all the linked records in Table 4. For each Table 4 recor:
Append query in Table 4.

The problem; this takes toooo long, with the number of records I have. I was wonderingn, whether I could do some kind of copy & paste in one go. The problem, is that i always need to know, what the higher level (new) key is, in order to update the lower level table.

Confused? so am I? what am I not seeing?

Many thanks

Jan

 

[dcb]

The first question is why are you doing this?
If its to warehouse this data (and clean your working tables) you should, IMHO, be de-normalizing your data....

Looking at your method:
Append query in Table 1, lookup the last created ID1.
Loop through all the linked records in Table 2. For each Table 2 record:

Why loop through table 2? Use a insert into ....... SELECT query then the db handles this in one go

Another thought is that if you are duplicating, and not creating new records in your duplicated tables.... simply take your ID's across with you:

PKID in Table1a = PKID in table1b

 

[jan@BRU]

Hello,

yes the first question - sorry I forget to mention it. The purpose of the whole process is to create a the starting base of something, that will be changed subesequently. So yes, initially it would be duplicated, but be modified aft rthat... so no problem with data integrity.

Well, the sedond point: that's the entire problem. If I were able to do, what you say in one go, that's what i am looking for. Problem is, that when the data initially is something like:

Table 1:
A, xxx
B, xxx

Table 2:
1, xxxx A
2, xxxx A
3, xxxx B
4, xxxxx B
5, xxxxx B

I want a result like:

Table 1:
A, xxx
B, xxx
C, xxx
D, xxx

Table 2:
1, xxxx A
2, xxxx A
3, xxxx B
4, xxxxx B
5, xxxxx B
6, xxxx C
7, xxxx C
8, xxxx D
9, xxxxx D
10, xxxxx D

The problem is that I need to know that record 8 of Table 2 is linked to D rather D in Table 1...

If you have a solution in one go, that works, please let me know.

many thanks

 

[dcb]

so you are looking to duplicate within the same table?

My answer applies:

Consider this query

INSERT INTO YourSubTable ( Some_Field, FKID_Field )
SELECT YourSubTable.Some_Field, 2 AS FKID
FROM YourSubTable
WHERE (((YourSubTable.FKID_Field)=1));

 

[jan@BRU]

Yes,
you are right, for this. unfortunately, it only works with one level, not with a cascade. Problem is that i need to know the newly created keys in (your) subtable, in order to do the same in subsubtable (Table 3 in my example)....