SQL
Multitable INSERT
#
Find similar titles
-
최초 작성자
jkpark@insilicogen.com
- 최근 업데이트
Structured data
- Category
- Database
Table of Contents
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