Multi table insert in Oracle
Posted by decipherinfosys on April 3, 2008
This feature has been available in Oracle since 9i. You may or may not have used it in your work. Here is an example:
create table TAB1 (COL1 NUMBER(30), COL2 NUMBER(30) );
create table TAB2 (COL3 NUMBER(30), COL4 DATE);
CREATE SEQUENCE TEST_SEQ INCREMENT BY 1;
when mod( object_id, 2 ) = 1 then
into TAB1 ( COL1, COL2 ) values ( test_seq.nextval, object_id )
when mod( object_id, 2 ) = 0 then
into TAB2 ( COL3, COL4 ) values ( test_seq.nextval, created )
select object_id, created from all_objects
The benefits are obvious – you run the select statement only once in a multi-table insert i.e. you incur the cost of the select statement only once as opposed to twice in the example above since if they were 2 statements, the cost incurred would have been twice – once for each insert into…select statement. There are variations to this statement like the “Insert All”…it takes the form:
select … from tableX;
You can read up more in Oracle docs here.
Sorry, the comment form is closed at this time.