Copy records from third table with foreign key to

Question

I have three tables: Table1 as master, Table2 as detail for table1 and Table3 as detail for Table2.

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 is auto incremented primary keys for table1, table2 and table3 respectively, while FK1 is a foreign key to PK1 and FK2 is a foreign key to PK2.

I need to copy one record from Table1 to the same table with all its detail records from Table2 and Table3.

I already did the copy for Table1 and Table2 using Insert Into...Select...Returning and I am thinking to copy Table2 and Table3 records inside FOR Select. Is there a better solution?

Answer 1

I don't think that you really have autoincremented primary keys. More likely you use generators. If so then you may use this way of inserting data:

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

if needed, you may use join or union tables in select query.

Just keep in mind to have proper output of your query to fit insert statement.