Oracle database 使用SQL转换数据 使用多表插入 理论试验

1、建立新表

create table new_employees5 as select * from new_employees3 where 1=2;
create table new_employees6 as select * from new_employees3 where 1=2;

2、从源表装载所有行，注意目标表的列要在源表的列中，即表列名要一致，否者会报 ORA-00904: "WORKING_SERVICE_TEAM_ID": invalid identifier的错误

insert all
into new_employees5 VALUES (employee_id,salary_new,department_id,last_name,first_name)
into new_employees6 VALUES (employee_id,salary_new,department_id,last_name,first_name)
select first_name, last_name, employee_id, salary_new, department_id from new_employees3;

3、带有条件的装载

drop table new_employees5 purge;
drop table new_employees6 purge;
把源表中的列 department_id 修改成为special_service_id
alter table new_employees3
rename column department_id to special_service_id;

装载：

insert all
when special_service_id=100 then
into new_employees5 VALUES (employee_id,salary_new,special_service_id,last_name,first_name)
when special_service_id=66 then
into new_employees6 VALUES (employee_id,salary_new,special_service_id,last_name,first_name)
select first_name, last_name, employee_id, salary_new, special_service_id from new_employees3;

查看装载结果：

SQL> select * from new_employees6;

EMPLOYEE_ID SALARY_NEW SPECIAL_SERVICE_ID LAST_NAME
----------- ---------- ------------------ -------------------------
FIRST_NAME
--------------------
	120	  1200		       66 binbin
fan

	120	  1200		       66 yifei
liu

	120	 96000		       66 weiyi
kong


SQL>