Systems Engineering and RDBMS

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) );

Table created.

create table TAB2 (COL3 NUMBER(30), COL4 DATE);

Table created.

CREATE SEQUENCE TEST_SEQ INCREMENT BY 1;

Sequence created.

insert
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:

insert all

into ….
into ….
select … from tableX;

You can read up more in Oracle docs here.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: