Inserting child table with parent table having generated column

I am sure we all have come across situations were we have to populate a child table with a value that has been generated in a parent table. If you don’t know how to handle it can take a bit of coding. Recently I came across a situation at work where a newbie was trying to populate a child table with an id that was generated at the parent table.
The girl first inserted the data to the parent table which has an id column defined as GENERATED ALWAYS. She then selected the row to retrieve the generated value and then used it to populate the child table.

Whats wrong with this ? Nothing wrong with it unless you don’t want to kill the I/O system on a heavily used table.  So I introduced her to the famous  select from insert statement. Yes you heard it right…select from insert.  Surely everybody knows  INSERT SELECT, but  SELECT from INSERT is a slick feature.  You can retrieve the data you inserted without doing any additional I/O.  Here is a small example :

Lets create two sample tables :

create table orders
(order_id  int not null
generated always as identity (start with 1000,increment by 1, nocache)
,customer_id int not null
,order_timestamp  timestamp
,order_channel_id  varchar(5)
,csr_id  varchar(10)
,primary key (order_id)
);
create table order_items
(order_id  int not null
,seq_num  int not null
,product_id  varchar(20) not null
,qty int
,unit_price dec(9,2)
,product_discount dec(9,2)
,primary key(order_id,seq_num)
);

Now lets try to add a row to orders table and then using the generated order_id we will add a row to the order_items table.

create or replace variable vseq int;
set vseq = 1;
create or replace variable vorder int;
set vorder =
(select order_id
from new table (
insert into orders
(customer_id,order_timestamp,order_channel_id,csr_id)
values(5000,current_timestamp,’web’,’raju’)));

insert into order_items
values(vorder,vseq,’A001′,10,100.00,0.00);

set vseq = vseq + 1;

insert into order_items
values(vorder,vseq,’B002′,25,35.50,5.50);

The reason why I added the vseq variable was to show you that it can also be done. Another thing you can do while you are still connected on the same session you can issue : select vorder from sysibm.sysdummy1   to retrieve the value of the variable.

The variable is dropped when the connection or session is terminated. However its a good practice to drop the variable at the end of the transactions :

DROP VARIABLE vorder;

You can use the same approach if you are using the sequences