Multiple Insert Statement in Oracle

Multiple Insert Statement:-
                            In multiple INSERT statement, you insert data into more then one tables as a part of single DML statement.
                           
                           
Type of Multiple Insert Statement:

    1.  Unconditional INSERT
    2.  Conditional INSERT
    3.  Conditional FIRST INSERT
    4.  Pivoting INSERT
   
Syntax:-

        INSERT [ALL] [CONDITION_INSERT_CLAUSE]
        [insert_into_clause values_clause ] (subquery);
       
    => Conditional_insert_clause

        [ALL] [FIRST]
        [WHEN condition THEN] [insert_into_clause values_clause]
        [else] [insert_into_clause values_clause];
       
       
       
Unconditional INSERT ALL:-
                        This INSERT statement is referred to as as unconditional INSERT because no further restriction is applied to the rows that are retrieved by SELECT statement.
                       
Example:-

        INSERT ALL
        INTO XXC05_EMP1(EMP_ID, L_NAME, SAL)
        INTO XXC05_EMP2(EMP_ID, F_NAME, MGR_ID)
        SELECT EMPLOYEE_ID EMP_ID,
               LAST_NAME L_NAME,
               FIRST_NAME F_NAME,
               SALARY SAL,
               MANAGER_ID MGR_ID
        FROM EMPLOYEES;
              
Note:- Insert data into XXC05_EMP1, XXC05_EMP2 tables by a single SELECT statement.


Conditional INSERT ALL:-
                        This INSERT statement is referred to as as conditional INSERT because a further restriction is applied to the rows that are retrieved by SELECT statement.
                       
Example:-

       INSERT ALL
        WHEN EMPLOYEE_ID <=200
        THEN
        INTO XXC05_EMP1(EMP_ID, L_NAME, SAL)
        VALUES(EMPLOYEE_ID, LAST_NAME, SALARY)
        WHEN EMPLOYEE_ID >200
        THEN
        INTO XXC05_EMP2(EMP_ID, F_NAME, MGR_ID)
        VALUES(EMPLOYEE_ID, FIRST_NAME, MANAGER_ID)
        SELECT EMPLOYEE_ID,
               LAST_NAME,
               FIRST_NAME,
               SALARY,
               MANAGER_ID
        FROM EMPLOYEES;
      
               

Conditional FIRST INSERT:-
                            This INSERT statement is referred to as as conditional FIRST INSERT because a further restriction is applied to the rows that are retrieved by SELECT statement. In that statement first true condition find and insert data in those table, rest of conditions or statement are switched.
                           
Example:-

    INSERT FIRST
    WHEN EMPLOYEE_ID <= 24000
    THEN
    INTO employee1(EMPLOYEE_ID, LAST_NAME, SALARY)
    WHEN EMPLOYEE_ID = 2388
    THEN
    INTO employee2(EMPLOYEE_ID, LAST_NAME, SALARY)
    WHEN EMPLOYEE_ID <=20000
    THEN
    INTO employee3(EMPLOYEE_ID, LAST_NAME, SALARY)
    WHEN EMPLOYEE_ID >=0
    THEN
    INTO employee4(EMPLOYEE_ID, LAST_NAME, SALARY)
    SELECT EMPLOYEE_ID,LAST_NAME,SALARY
    FROM EMPLOYEES;                           
   
Note:- In above example First condition is true so data insert into only EMPLOYEE1 table.It do not check any other conditions.


Example:-

    INSERT FIRST
    WHEN EMPLOYEE_ID <= -24000
    THEN
    INTO employee1(EMPLOYEE_ID, LAST_NAME, SALARY)
    WHEN EMPLOYEE_ID = -2388
    THEN
    INTO employee2(EMPLOYEE_ID, LAST_NAME, SALARY)
    WHEN EMPLOYEE_ID <=20000
    THEN
    INTO employee3(EMPLOYEE_ID, LAST_NAME, SALARY)
    WHEN EMPLOYEE_ID >=0
    THEN
    INTO employee4(EMPLOYEE_ID, LAST_NAME, SALARY)
    SELECT EMPLOYEE_ID,LAST_NAME,SALARY
    FROM EMPLOYEES;                           
   
Note:- In above example THIRD condition is true so data insert into only EMPLOYEE3 table. It do not check FORTH condition.
              
              
              
Pivoting INSERT:-
                Using pivoting INSERT, convert the set of sales records from nonrelational database table to relational format.