ORACLE|오라클 Multiple Insert - Insert All

Insert All

-  Oracle 9i 이상에서 지원.

- Multitable insert는 한개의 테이블에 여러 row를 넣거나, 여러개의 테이블에 데이터를 한 insert 명령으로 넣을 수 있습니다.  이전 버전에서는 이런 기능은 PL/SQL로 구현을 해야 했으나 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;                                                 
                                

* 조거엔 따른 Insert

- 모든 데이터를 같은/다른 테이블에 multi-row의 insert 가 가능하지만 특정 condition에 따라 insert도 가능합니다. 이 경우 INSERT FIRST를 사용하는데 이는 한개의 조건에 만족할 경우 나머지 조건은 skip되어 수행됩니다. 반대로 INSERT ALL의 경우 모든 조건을 판단하게 됩니다. 

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