[Oracle] 다중테이블 insert

다중테이블 insert

: source 테이블에서 데이터를 추출해서 여러개의 target

-ETL(Extraction(추출), Transformation(변형), Loading(적재))

-source 시스템에서 데이터 추출해서 data warehouse로 가져오는 작업

source table(employees) --------> target table(sal_history,mgr_history)

        values                 ETL작업                     into

 

1. 무조건 insert all

<예시테이블 생성>

create table sal_history
as select employee_id, hire_date, salary from employees where 1 = 2;
select * from sal_history;

create table mgr_history
as select employee_id, manager_id, salary from employees where 1=2;
select * from mgr_history;

insert 사용할 시 ⇒ 성능↓

insert into sal_history(employee_id,hire_date,salary) 
select employee_id, hire_Date, salary 
from employees; 
select * from sal_history;

insert into mgr_history(employee_id,manager_id,salary) 
select employee_id, manager_id, salary 
from employees; 
select * from mgr_history;

<무조건 insert all>

insert all

into

(target table 컬럼) values(source table 컬럼)

select * from source table;

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*1.1 sal
from employees;

select * from sal_history; 
select * from mgr_history;

 

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

insert all 

when 조건 then

into target table( ) values(source table 컬럼)

select * from source table;

insert all 
when day < to_date('2005-01-01','yyyy-mm-dd') and sal >= 5000 then 
	into emp_history(employee_id,hire_date,salary) values(id,day,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;

select * from emp_history; 
select * from emp_sal;

• emp_sal, emp_history에 모두 존재하는 사원 출력

select * from emp_history e
where exists (select 'x' 
		from emp_sal 
   	 	where employee_id = e.employee_id);

 

3. 조건 first insert

<예시테이블 생성> 

create table sal_low as select employee_id, last_name, salary from employees where 1=2;
create table sal_mid as select employee_id, last_name, salary from employees where 1=2;
create table sal_high as select employee_id, last_name, salary from employees where 1=2;

<조건 insert first>

insert first

when 조건 then

into target table( ) values(source table 컬럼)

else

into target table( ) values(source table 컬럼)

select * from source table;

insert first
when sal < 5000 then
	into sal_low(employee_id,last_name,salary) values(id,name,sal)
when sal between 5000 and 10000 then
	into sal_mid(employee_id,last_name,salary) values(id,name,sal)
else
	into sal_high(employee_id,last_name,salary) values(id,name,sal)
select employee_id id, last_name name, salary sal from employees;

select * from sal_low;
select * from sal_mid;
select * from sal_high;

---

[문제95] 사원들 중에 근무연수가 15년 이상 이면서 급여는 10000이상 급여를 받는 사원들은

emp_1테이블에 사번, 이름(last_name), 입사일, 근무연수, 급여 정보를 입력하고

근무연수가 15년 이상 이면서 급여는 10000미만 급여를 받는 사원들은

emp_2테이블에 사번, 이름(last_name), 입사일, 근무연수, 급여 정보를 입력하세요.

create table emp_1(id number, name varchar2(30), day date, years number, sal number);
create table emp_2(id number, name varchar2(30), day date, years number, sal number);