Oracle 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;