DataBase > Oracle/Tibero

다수의 레코드를 한번에 insert 하기 (insert multiple rows)

하나의 테이블에 여러 데이터를 insert (like 배치)

방식1) 조건으로 추려내서 삽입할 경우 (조건 없으면 방식2와 동일)

insert into tbl(col1, col2, col3)
    with alias_x as (
        select 'a1', 'a2', 'a3' as col3 from dual union all
        select 'b1', 'b2', 'b3' as col3 from dual union all
        select 'c1', 'c2', 'c3' as col3 from dual union all
        select 'd1', 'd2', 'd3' as col3 from dual
    )
    select * from alias_x
    where col3 like 'c%';

방식2) 일괄 삽입하는 방식

INSERT ALL
    INTO TBL(COL1, COL2, COL3) VALUES('a1', 'a2', 'a3')
    INTO TBL(COL1, COL2, COL3) VALUES('b1', 'b2', 'b3')
    INTO TBL(COL1, COL2, COL3) VALUES('c1', 'c2', 'c3')
    INTO TBL(COL1, COL2, COL3) VALUES('d1', 'd2', 'd3')
SELECT 1 FROM DUAL;

 

각기 다른 여러 테이블 여러 데이터를 insert

방식1) 여러 테이블에 각각 다른 데이터를 넣기

각각 row에서 테이블을 지정한다.

 INSERT ALL
    INTO TBL(COL1, COL2, COL3) VALUES('a1', 'a2', 'a3')
    INTO TBL(COL1, COL2, COL3) VALUES('b1', 'b2', 'b3')
    INTO TBL2(COL1, COL2, COL3) VALUES('c1', 'c2', 'c3')
    INTO TBL3(COL1, COL2, COL3) VALUES('d1', 'd2', 'd3')
SELECT 1 FROM DUAL;

방식2) 여러 테이블에 각각 다른 데이터를 선별해 삽입

INSERT ALL
    INTO TBL1(COL1, COL2, COL3) VALUES(COL1, COL2, COL3)
    INTO TBL2(COL1, COL2, COL3) VALUES(COL1, COL2, COL3)
    INTO TBL3(COL1, COL2) VALUES(COL1, COL2, COL3)
    INTO TBL4(COL1) VALUES(COL1, COL2)
    
    WITH ALIAS_X AS (
        select 'a1' as col1, 'a2' as col2, 'a3' as col3 from dual union all
        select 'b1' as col1, 'b2' as col2, 'b3' as col3 from dual union all
        select 'c1' as col1, 'c2' as col2, 'c3' as col3 from dual union all
        select 'd1' as col1, 'd2' as col2, 'd3' as col3 from dual
    ) SELECT * FROM ALIAS_X;

방식3) 여러 데이터를 조건에 따라 각각 테이블에 삽입

INSERT ALL
    WHEN 1=1 THEN
        INTO TBL1(COL1, COL2, COL3) VALUES(COL1, COL2, COL3)
        
    WHEN COL3 IS NOT NULL THEN
        INTO TBL2(COL1, COL2, COL3) VALUES(COL1, COL2, COL3)
        
    WHEN COL3 IS NULL THEN    
        INTO TBL3(COL1, COL2) VALUES(COL1, COL2, COL3)
        INTO TBL4(COL1) VALUES(COL1, COL2)
    
    WITH ALIAS_X AS (
        select 'a1' as col1, 'a2' as col2, 'a3' as col3 from dual union all
        select 'b1' as col1, 'b2' as col2, 'b3' as col3 from dual union all
        select 'c1' as col1, 'c2' as col2, 'c3' as col3 from dual union all
        select 'd1' as col1, 'd2' as col2, 'd3' as col3 from dual
    ) SELECT * FROM ALIAS_X;

 

[함수] TRUNC 잘라버리기 ❮
❯ parallel hint