第十七单元：INSERT 增强

一个来源插入多个目标表（无条件）:

insert all
    into sal_history values(empid,hiredate,sal)
    into mgr_history values(empid,mgr,sal)
    select employee_id empid,hire_date hi
    redate,salary sal,manager_id mgr
    from employees;

一个来源插入多个目标表（有条件):

insert all
    into sal_history values(empid,hiredate,sal)
    into mgr_history values(empid,mgr,sal)
    select employee_id empid,hire_date hi
    redate,salary sal,manager_id mgr
    from employees
    where employee_id > 200;

一个来源插入多个目标表（有条件，首次匹配即跳到下一条):

insert first
    when sal > 25000 then 
        into special_sal values(deptid,sal)
    when hiredate like ('%00%') then
        into hiredate_history_00 values(deptid,hiredate)
    when hiredate like ('%99%') then 
        into hiredate_history_99 values(deptid,hiredate)
    else 
        into hiredate_history values(deptid,hiredate)
    select department_id deptid,sum(salary) sal,max(hire_date) hiredate
    from employees
    group by department_id;

列转行（一行变多行，交叉报表的反操作）:

insert all 
    into sales_info values (employee_id,week_id,sales_mon)
    into sales_info values (employee_id,week_id,sales_tue)
    into sales_info values (employee_id,week_id,sales_wed)
    into sales_info values (employee_id,week_id,sales_thur)
    into sales_info values (employee_id,week_id,sales_fri)
    select employee_id,week_id,sales_mon,sales_tue,sales_wed,sales_thur,sales_fri
    from sales_source_data;

sales_source_data表

插入数据后的sales_info 表