Insert into multiple tables using single SELECT Query

Multi table Insert is a extension to INSERT...SELECT .

There are two types of Multi table Inserts

·         INSERT FIRST

·         INSERT ALL

Syntax :

INSERT ALL|FIRST

   [WHEN condition THEN] INTO target [VALUES]

   [WHEN condition THEN] INTO target [VALUES]

   ...

   [ELSE] INTO target [VALUES]

SELECT ...

FROM   source_query;

Example:

SQL> SELECT * FROM EMP;

SQL> SELECT DEPTNO,COUNT(1) FROM EMP GROUP BY DEPTNO;

SQL> CREATE TABLE EMP_10 AS SELECT * FROM EMP WHERE 1 =2 ;

SQL> CREATE TABLE EMP_20 AS SELECT * FROM EMP_10;

SQL> CREATE TABLE EMP_30 AS SELECT * FROM EMP_10;

SQL> CREATE TABLE EMP_OTHER AS SELECT * FROM EMP_10;

Inserting all records in Multiple tables :

SQL> Insert All

                into EMP_10

                into EMP_20

                into EMP_30

        select * from emp;

SQL> SELECT * FROM EMP_10;

SQL> SELECT * FROM EMP_20;

SQL> SELECT * FROM EMP_30;

Condition based Insert in Multiple tables :

Insert FIRST

                WHEN Deptno = 10

                THEN INTO  EMP_10

                WHEN Deptno = 20

                THEN INTO EMP_20

                WHEN Deptno = 30

                THEN INTO EMP_30

                ELSE

                INTO EMP_OTHER

SELECT * FROM EMP

We can also insert Particular Columns Data:

Insert FIRST

                WHEN Deptno = 10

                THEN INTO  EMP_10(empno,sal)

                                values (empno,sal)

                WHEN Deptno = 20

                THEN INTO EMP_20(empno,sal)

                                values (empno,sal)

                WHEN Deptno = 30

                THEN INTO EMP_30(empno,sal)

                                values (empno,sal)

                ELSE

                INTO EMP_OTHER

SELECT * FROM EMP