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.
\nThe 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.<\/p>\n
Whats wrong with this ? Nothing wrong with it unless you don’t want to kill the I\/O system on a heavily used table. \u00c2\u00a0So I introduced her to the famous \u00c2\u00a0select from insert statement. Yes you heard it right…select from insert. \u00c2\u00a0Surely everybody knows \u00c2\u00a0INSERT SELECT, but \u00c2\u00a0SELECT from INSERT is a slick feature. \u00c2\u00a0You can retrieve the data you inserted without doing any additional I\/O. \u00c2\u00a0Here is a small example :<\/p>\n
Lets create two sample tables :<\/p>\n
create table orders<\/span> 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.<\/p>\n create or replace variable vseq int;<\/span> insert into order_items<\/span> set vseq = vseq + 1;<\/span><\/p>\n insert into order_items<\/span> 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 :\u00c2\u00a0select vorder from sysibm.sysdummy1<\/span>\u00c2\u00a0\u00c2\u00a0 to retrieve the value of the variable.<\/p>\n 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 :<\/p>\n DROP VARIABLE vorder;<\/span><\/p>\n You can use the same approach if you are using the sequences<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[3,358],"tags":[796,790,794,795,792,798,797,793,791],"class_list":["post-1313","post","type-post","status-publish","format-standard","hentry","category-db2","category-how-to","tag-autogenerated-parent-key","tag-generated-column","tag-insert-child-table-with-generated-value","tag-insert-child-table-with-parent-key","tag-inserting-child-table","tag-retreiving-insert-value","tag-retrieving-generated-value","tag-select-insert","tag-sequence"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1313","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1313"}],"version-history":[{"count":3,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1313\/revisions"}],"predecessor-version":[{"id":1317,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1313\/revisions\/1317"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1313"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1313"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1313"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n (order_id\u00c2\u00a0 int not null<\/span>
\n generated always as identity (start with 1000,increment by 1, nocache)<\/span>
\n ,customer_id int not null<\/span>
\n ,order_timestamp\u00c2\u00a0 timestamp<\/span>
\n ,order_channel_id\u00c2\u00a0 varchar(5)<\/span>
\n ,csr_id\u00c2\u00a0 varchar(10)<\/span>
\n ,primary key (order_id)<\/span>
\n );<\/span>
\n create table order_items<\/span>
\n (order_id\u00c2\u00a0 int not null<\/span>
\n ,seq_num\u00c2\u00a0 int not null<\/span>
\n ,product_id\u00c2\u00a0 varchar(20) not null<\/span>
\n ,qty int<\/span>
\n ,unit_price dec(9,2)<\/span>
\n ,product_discount dec(9,2)<\/span>
\n ,primary key(order_id,seq_num)<\/span>
\n );<\/span><\/p>\n
\n set vseq = 1;<\/span>
\n create or replace variable vorder int;<\/span>
\n set vorder =<\/span>
\n (select order_id<\/span>
\n from new table (<\/span>
\n insert into orders<\/span>
\n (customer_id,order_timestamp,order_channel_id,csr_id)<\/span>
\n values(5000,current_timestamp,’web’,’raju’)));<\/span><\/p>\n
\n values(vorder,vseq,’A001′,10,100.00,0.00);<\/span><\/p>\n
\n values(vorder,vseq,’B002′,25,35.50,5.50);<\/span><\/p>\n