다중 인서트 insert all 오라클 쿼리문

Oracle 9i 이상에서 지원 됩니다.

 -- Unconditional insert into ALL tables                                 

INSERT ALL                                                             
  INTO sal_history VALUES(empid,hiredate,sal)                          
  INTO mgr_history VALUES(empid,mgr,sysdate)                           
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
  FROM employees WHERE employee_id > 200;                              
                                                                       
-- Pivoting insert to split non-relational data                        
INSERT ALL                                                             
  INTO Sales_info VALUES (employee_id,week_id,sales_MON)               
  INTO Sales_info VALUES (employee_id,week_id,sales_TUE)               
  INTO Sales_info VALUES (employee_id,week_id,sales_WED)               
  INTO Sales_info VALUES (employee_id,week_id,sales_THUR)              
  INTO Sales_info VALUES (employee_id,week_id, sales_FRI)              
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,                     
    sales_WED, sales_THUR,sales_FRI                                    
FROM Sales_source_data;                                                
                                                                       
-- Conditionally insert into ALL tables                                
INSERT ALL                                                             
  WHEN SAL>10000 THEN                                                  
    INTO sal_history VALUES(EMPID,HIREDATE,SAL)                        
  WHEN MGR>200 THEN                                                    
    INTO mgr_history VALUES(EMPID,MGR,SYSDATE)                         
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
  FROM employees WHERE employee_id > 200;                              
                                                                       
-- Insert into the FIRST table with a matching condition               
INSERT FIRST                                                           
  WHEN SAL > 25000  THEN                                               
    INTO special_sal VALUES(DEPTID,SAL)                                
  WHEN HIREDATE like ('%00%') THEN                                     
    INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)                   
  WHEN HIREDATE like ('%99%') THEN                                     
    INTO hiredate_history_99 VALUES(DEPTID,HIREDATE)                   
  ELSE                                                                 
    INTO hiredate_history VALUES(DEPTID, HIREDATE)                     
SELECT department_id DEPTID, SUM(salary) SAL,                          
    MAX(hire_date) HIREDATE                                            
  FROM employees GROUP BY department_id;