다중 테이블 INSERT

1. 무조건 insert all

 

# 기존코드

# 테이블 만들기 (sal_history, mgr_history) / 테이블의 뼈대만 만들어 놓는다.

create table sal_history

as select employee_id, hire_date, salary

    from employees

    where 1 = 2;

 

create table mgr_history

as select employee_id, manager_id, salary

    from employees

    where 1 = 2;

 

# 만들어진 테이블 뼈대 안에 데이터를 집어넣는다.

insert into sal_history

select employee_id, hire_date, salary

from employees;

 

insert into mgr_history

select employee_id, hire_date, salary

from employees;

 

# 만든 테이블 결과 확인

select * from sal_history;

select * from mgr_history;

rollback;

 

위와같은 코드는

동일한 소스 테이블에서 이용해서 여러개의 타겟 테이블을 만드는데

이 과정에서 소스 테이블을 불필요하게 여러번 불러들인다.

---

insert all을 사용하여 더 효율적으로 코드를 만들어보자.

 

insert all

into sal_history(employee_id, hire_date, salary)

      values(id, day, sal)

into mgr_history(employee_id, manager_id, salary)

      values(id, mgr, sal)

select employee_id id, hire_date day, manager_id mgr, salary sal

from employees;

 

# 결과 확인

select * from sal_history;

select * from mgr_history;

rollback;

 

더 간략한 코드로 같은 결과를 낼 수 있다.

---

 

2. 조건 insert all

 

# 기존코드

create table emp_history

as select employee_id, hire_date, salary

    from employees

    where 1 = 2;

 

create table emp_sal

as select employee_id, commission_pct, salary

    from employees

    where 1 = 2;

 

insert into emp_history

select employee_id, hire_date, salary

from employees

where hire_date < to_date('2005-01-01','yyyy-mm-dd');

 

insert into emp_sal

select employee_id, commission_pct, salary

from employees

where commission_pct is not null;

 

select * from emp_history;

select * from emp_sal;

rollback;

 

소스가 동일한데 조건에 따라 들어갈수있는 타겟테이블이 다름(조건이 맞으면 여러개 들어갈수도 있음)

---

insert all

when day < to_date('2005-01-01','yyyy-mm-dd') then

        into emp_history(employee_id, hire_date, salary)

        values(id, date, sal)

when comm is not null then

        into emp_sal(employee_id, commission_pct, salary)

        values(id, comm, sal)

select employee_id id, hire_date day, salary sal, commission_pct comm

from employees;

---

 

3. 조건 first insert

 

# 기존코드

# 급여가 5000원 미만 = sla_low

create table sal_low

as select employee_id, last_name, salary

from employees

where 1 = 2;

 

# 급여가 5000원 ~ 10000원 = sal_mid

create table sal_mid

as select employee_id, last_name, salary

from employees

where 1 = 2;

 

# 급여가 10000원 초과 = sal_mid

create table sal_high

as select employee_id, last_name, salary

from employees

where 1 = 2;

 

insert into sal_low

select employee_id, last_name, salary

from employees

where salary < 5000;

 

insert into sal_mid

select employee_id, last_name, salary

from employees

where salary between 5000 and 10000;

 

insert into sal_high

select employee_id, last_name, salary

from employees

where salary > 10000;

 

select * from sal_high;

 

rollback;

---

조건이 다를때 사용

insert first

when salary < 5000 then

         into sal_low(employee_id, last_name, salary)

         values(employee_id, last_name, salary)

when salary between 5000 and 10000 then

         into sal_mid(employee_id, last_name, salary)

         values(employee_id, last_name, salary)

else

         into sal_high(employee_id, last_name, salary)

         values(employee_id, last_name, salary)

select employee_id, salary, last_name

from employee;

 

# 결과 확인

select * from sal_low;

select * from sal_mid;

select * from sal_high;

rollback;