Multitable INSERT #

여러 테이블에 여러 ROW를 동시에 등록할 때 사용된다.

Unconditional INSERT ALL #

• 서브쿼리에서 확보한 데이터 전체를 여러 테이블에 등록할 때 사용한다.

문법 #

INSERT ALL
    INTO t_table1 (column1, column2, column3 ...) 
    VALUES (column1, column2, column3 ...)
    INTO t_table2 (column1, column2, column3 ...) 
    VALUES (column1, column2, column3 ...)
    INTO t_table3 (column1, column2, column3 ...) 
    VALUES (column1, column2, column3 ...)
    ...
SELECT column1, column2, column3 ... 
FROM f_table;

예제 #

INSERT ALL
    INTO t1 (col1, col2, col3) values (t4.col1, t4.col2, t4.col3)
    INTO t2 (col1, col2, col4) values (t4.col1, t4.col2, t4.col4)
    INTO t3 (col2, col3, col4) values (t4.col2, t4.col3, t4.col4)
SELECT col1, col2, col3, col4 FROM t4

Conditional INSERT ALL #

• 서브쿼리에서 확보한 데이터를 조건에 맞게 여러 테이블에 등록한다.

문법 #

INSERT ALL
    WHEN (condition) THEN
        INTO t_table1 (column1, column2, column3 ...)
        VALUES        (column1 ,column1, column1 ...)
    WHEN (condition) THEN
        INTO t_table2 (column1, column2, column3 ...)
        VALUES        (column1, column1, column1 ...)
    WHEN (condition) THEN
        INTO t_table3 (column1, column2, column3 ...)
        VALUES        (column1, column1, column1 ...)
SELECT column1, column2, column3 ... 
FROM f_table;

예제 #

INSERT ALL
    WHEN (t4.points<=80) THEN
        INTO t_NICE (col1, col2, col3) values (t4.col1, t4.col2, t4.col3)
    WHEN (t4.points<=90) THEN
        INTO t_GOOD (col1, col2, col4) values (t4.col1, t4.col2, t4.col4)
    WHEN (t4.points<=100) THEN
        INTO t_BEST (col2, col3, col4) values (t4.col2, t4.col3, t4.col4)
SELECT col1, col2, col3, col4. points FROM t4

Conditional INSERT FIRST #

• 서브쿼리에서 확보한 데이터 중 가장 첫 번째 조건에 맞는 테이블에 등록한다.

문법 #

INSERT FIRST
    WHEN (condition) THEN
        INTO t_table1 (column1, column2, column3 ...)
        VALUES        (column1, column2, column3 ...)
    WHEN (condition) THEN
        INTO t_table2 (column1, column2, column3 ...)
        VALUES        (column1, column2, column3 ...)
    WHEN (condition) THEN
        INTO t_table3 (column1, column2, column3 ...)
        VALUES        (column1, column2, column3 ...)
    ...
    ELSE
        INTO t_table3 (column1, column2, column3 ...)
        VALUES        (column1, column2, column3 ...)
SELECT column1, column2, column3 ... 
FROM f_table;

예제 #

INSERT FIRST
    WHEN (t4.points>=100) THEN
        INTO t_BEST (col1, col2, col3) values (t4.col1, t4.col2, t4.col3)
    WHEN (t4.points>=90) THEN
        INTO t_GOOD (col1, col2, col4) values (t4.col1, t4.col2, t4.col4)
    WHEN (t4.points>=80) THEN
        INTO t_NICE (col2, col3, col4) values (t4.col2, t4.col3, t4.col4)
    ELSE
        INTO t_WELL (col2, col3, col4) values (t4.col2, t4.col3, t4.col4)
SELECT col1, col2, col3, col4. points FROM t4

References #

• http://allthingsoracle.com/multi-table-insert-statements-in-oracle/
• http://www.oracle-developer.net/display.php?id=209