Multi-Table In oracle database.

Multi-table insert is a new feature of Oracle 9i Release 1 (9.0). An extension to INSERT..SELECT, this feature enables us to define multiple insert targets for a source data.

SQL> CONN RND/rnd
Connected.
SQL>
SQL>
SQL>
SQL> CREATE TABLE MULTI1 (ID NUMBER, NAME VARCHAR2(300), SALARY NUMBER);

Table created.

SQL>
SQL> CREATE TABLE MULTI2(ID NUMBER, NAME VARCHAR2(300), SALARY NUMBER);

Table created.

SQL>
SQL> INSERT ALL
  2  WHEN EMPLOYEE_ID >=140 THEN
  3  INTO MULTI1(ID, NAME, SALARY)
  4  VALUES (EMPLOYEE_ID, FIRST_NAME, SALARY)
  5  ELSE
  6  INTO MULTI2(ID, NAME, SALARY)
  7  VALUES (EMPLOYEE_ID, FIRST_NAME, SALARY)
  8  SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
  9  FROM EMP;

107 rows created.

SQL>
SQL> SELECT MAX(ID), MIN(ID)  FROM MULTI1;

   MAX(ID)    MIN(ID)
---------- ----------
       206        140

SQL>
SQL> SELECT MAX(ID), MIN(ID)  FROM MULTI2;

   MAX(ID)    MIN(ID)
---------- ----------
       139        100

SQL>