Multiple Insert 문제

[허 진경]

-- Employees 테이블의 사원들 정보를 아래의 두 테이블에 나눠 저장하세요.
--1. emp_personal_info 테이블에는 employee_id, first_name, last_name,
--   email, phone_number가 저장되도록 하세요.
--2. emp_office_info 테이블에는 employee_id, hire_date, 
--   salary, commission_pct, manager_id, department_id가 저장되도록 하세요.

CREATE TABLE emp_personal_info AS 
    SELECT employee_id, first_name, last_name, email, phone_number
    FROM employees
    WHERE 1=2;

CREATE TABLE emp_office_info AS
    SELECT employee_id, hire_date, salary, commission_pct, 
           manager_id, department_id
    FROM employees
    WHERE 1=2;

INSERT ALL
    INTO emp_personal_info
        VALUES (employee_id, first_name, last_name, email, phone_number)
    INTO emp_office_info
        VALUES (employee_id, hire_date, salary, commission_pct, 
                manager_id, department_id)
    SELECT * FROM employees;

SELECT * FROM emp_personal_info;
SELECT * FROM emp_office_info;

--- Employees 테이블의 사원들 정보를 아래의 두 테이블에 나눠 저장하세요.
--1. 보너스가 있는 사원들의 정보는 emp_comm 테이블에 저장하세요.
--2. 보너스가 없는 사원들의 정보는 emp_nocomm 테이블에 저장하세요.
CREATE TABLE emp_comm AS SELECT * FROM employees WHERE 1=2;
CREATE TABLE emp_nocomm AS SELECT * FROM employees WHERE 1=2;

INSERT ALL
    WHEN commission_pct IS NULL THEN
        INTO emp_comm
        VALUES (employee_id, first_name, last_name, email,
                phone_number, hire_date, job_id, salary, 
                commission_pct, manager_id, department_id)
    WHEN commission_pct IS NOT NULL THEN
        INTO emp_comm
        VALUES (employee_id, first_name, last_name, email,
                phone_number, hire_date, job_id, salary, 
                commission_pct, manager_id, department_id)
    SELECT * FROM employees;

[글쓴이]

글