Multi Table Insert Statement

Multi Table Insert:

Its very important concept to know when we have scenario to insert data to multiple tables based on single query output.

say eg: Usually we use external table to read data from local files and then to load to target tables.

so here instead of writing complex program to load data from external tables to Target tables ,we can simply use Multi table insert concept.

Lets go through examples.

Assume our scenario is load data from EMP table (In SCOTT Schema) data to another 2 different target tables.

(Note: Here you can take your external table also instead of EMP table).

Creating 2 target tables as below:


create table dest_table1 as select * from emp where 1=2;



create table dest_table2 as select * from emp where 1=2;

Case 1: Unconditional Insert All

When we are using Unconditional Insert All, the results which are returned by query(Driving Query) will insert as new record in target tables which are specified in INTO clauses.




select * from emp where rownum<=4

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING        PRESIDENT           11/17/1981   5000.00               10
 7698 BLAKE      MANAGER    7839  5/1/1981      2850.00               30
 7782 CLARK      MANAGER    7839  6/9/1981      2450.00               10

 7566 JONES      MANAGER    7839  4/2/1981      2975.00               20


SQL> INSERT ALL
         INTO dest_table1 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
             INTO dest_table2 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
    SELECT * FROM emp
    where rownum<=4;


8 rows inserted

In  Above example ,the Driving query returns 4 records so each target table(which are in INTO clause) will get 4 new records) so total 8 records for 2 tables. 

So basically here we are not applying any condition using "INSERT ALL", it means whatever records are coming from driving query will get inserted to target tables.

So how can we achieve if we have scenario to insert records based on condition ,Now we have to go for case 2.

Case 2: Conditional Insert ALL

In this case, we can add conditions to the INTO clause.Its looks like a CASE statement but here each condition is always tested for current row from the Driving query.

SQL> INSERT ALL
     WHEN DEPTNO=10 THEN //This case will be evaluated 4 times as we 4 records in driving query even though for deptno 10 records are 2.
           INTO dest_table1 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
      WHEN DEPTNO IN (20,30) THEN
 //This case will be evaluated 4 times as we 4 records in driving query even though for deptno 20,30 records are 2.
              INTO dest_table2 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
    SELECT * FROM emp
    where rownum<=4;


4 rows inserted

Hope you got clarity on how Conditional Insert All works.

In below case ,we added 1=1 at the end ,It means its always true so all records will be inserted into target table here.

SQL> INSERT ALL
   WHEN DEPTNO=10 THEN //here 2 records will be inserted
        INTO dest_table1  
VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
      WHEN DEPTNO IN (20,30) THEN  //here 2 records will be inserted
              INTO dest_table2
VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
   WHEN 1=1 THEN //here 4 records will be inserted
              INTO dest_table2 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
    SELECT * FROM emp
    where rownum<=4;


8 rows inserted

Case 3: Insert First

In this case conditions are tested until First Match is found and no further conditions are tested.

Lets take EMP table itself to understand clearly.

SQL> select * from emp
  where rownum<=4;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING        PRESIDENT          11/17/1981    5000.00               10
 7698 BLAKE      MANAGER    7839 5/1/1981      2850.00                 30
 7782 CLARK      MANAGER    7839 6/9/1981      2450.00                 10

 7566 JONES      MANAGER    7839 4/2/1981      2975.00                 20

SQL> INSERT ALL
     WHEN SAL>4000 THEN
         INTO dest_table1 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
      WHEN SAL>2500 THEN
              INTO dest_table2 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
   SELECT * FROM emp
    where rownum<=4;


4 rows inserted

I have taken INSERT ALL case here first and then will show INSERT FIRST.

As Per INSERT ALL,each condition is always tested for current row. so "dest_table1 " table will have only one record i.e with Salary of 5000/- but another table "dest_table2" will have 3 records i.e. with salary of 5000,2850,2975. Here if you observe 5000 salary record is getting inserted in 2nd table also even though it got inserted in first table so to avoid this we have INSERT FIRST case.

SQL> INSERT FIRST
    WHEN SAL>4000 THEN //here only one record
         INTO dest_table1 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
      WHEN SAL>2500 THEN //here only 2 records
            INTO dest_table2 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
    SELECT * FROM emp
    where rownum<=4;
  

3 rows inserted


The restrictions on multi table inserts are as follows.

• Multi table inserts can only be performed on tables, not on views or materialized views.
• You cannot perform a multi table insert via a DB link.
• You cannot perform multi table inserts into nested tables.
• The sum of all the INTO columns cannot exceed 999.
• Sequences cannot be used in the multi table insert statement. It is considered a single statement, so only one sequence value will be generated and used for all rows.
• Multi table inserts can't be used with plan stability.
• If the PARALLEL hint is used for any target tables, the whole statement will be parallelized. If not, the statement will only be parallelized if the tables have PARALLEL defined.
• Multi table statements will not be parallelized if any of the tables are index-organized, or have bitmap indexes defined on them.