Copying records from a third table with a foreign key to another table

Question

I have three tables: Table 1 as master, Table2 as detail for Table 1, and Table3 as detailed information for Table 2.

TABLE1 PK1 INTEGER, FD1 VARCHAR(100) TABLE2 PK2 INTEGER, FK1 INTEGER, FD2 VARCHAR(100) TABLE3 PK3 INTEGER, FK2 INTEGER, FD3 VARCHAR(100) 

PK1, PK2, PK3 are the automatically increasing primary keys for tables 1, table2 and table3, respectively, while FK1 is the foreign key for PK1, and FK2 is the foreign key for PK2.

I need to copy one record from table 1 to the same table with all its detailed records from Table 2 and Table 3.

I already made a copy for Table1 and Table2 using Insert Into...Select...Returning , and I'm going to copy the records of Table2 and Table3 inside FOR Select . Is there a better solution?

Answer 1

I don’t think you really have auto-incremental primary keys. Most likely you are using generators. If so, you can use this method of inserting data:

 insert into table1(pk1, fd1) select gen_id(g_pk1,1), fd1 from table2 

if necessary, you can use join or join tables in the selected query.

Just keep in mind that the correct output of your query is suitable for the insert statement.