How do copy row from one table to another with dependencies?

Question

I have some numbers of tables with foreign key between them. Structure of table is not important. For example, table A is a top level table. Table B and C have foreign key on table A and tables D, E and F and G have foreign key on table B and C resp. The question is: is there a way for clone one of row from table A with all depeddencies (row in tables B - G) using SQL?

Answer 1

You didn't provide structure nor data, so you will have to work with my example. The basic idea is just selecting the appropriate FK columns to the respective PK columns as you progress from parent to child.

-- create base tables
create table a (a_id integer, a_col1 text, constraint a_pk primary key (a_id)); 
create table b (b_id integer, a_id integer, b_col1 text
               , constraint b_pk primary key (b_id)
               , constraint b_fk_a foreign key (a_id)
                 references a(a_id)
               );
create table g (g_id integer, b_id integer, g_col1 text
               , constraint g_pk primary key (g_id)
               , constraint g_fk_b foreign key (b_id)
                 references b(b_id)
               );       

-------------------------------------------------------------------------------------------------------
-- populate
insert into a (a_id,a_col1)      values (11,'ta1'), (12,'ta2');
insert into b (b_id,a_id,b_col1) values (21,11,'tb1'), (22,11,'tb2'), (23,12,'tb3'), (24,12,'tb4');  
insert into g (g_id,b_id,g_col1) values (71,21,'tg1'), (72,21,'tg2'), (73,22,'tg3'), (74,22,'tg4')
                                      , (75,23,'tg5'), (76,23,'tg6'), (77,24,'tg7'), (78,24,'tg8');  
-------------------------------------------------------------------------------------------------------
-- Soution: clone a_id 12
create table a_clone as 
       select * 
         from a 
        where a_id = 12;

create table b_clone as 
       select * 
         from b 
      where a_id = 12; 

create table g_clone as 
       select * 
         from g
        where b_id in
              (select b_id
                 from b 
                where a_id = 12
              );
-- done 

The 'comes to mind' suggestion from Sticky Bit is doable, but it's not straight forward. I believe the above it straight forward.