1、无条件INSERT
首先建一张表
create table EMP
(
id VARCHAR2(4),
name VARCHAR2(50),
sal NUMBER(7,2),
money NUMBER(7,2),
deptno VARCHAR2(4),
createdate DATE default sysdate
)
再建立如下两张表
create table EMP1
(
id VARCHAR2(4),
name VARCHAR2(50),
sal NUMBER(7,2)
)
create table EMP2
(
id VARCHAR2(4),
name VARCHAR2(50),
money NUMBER(7,2)
)
无条件插入:
insert all
into emp1(id,name,sal) values(id,name,sal)
into emp2(id,name,money) values(id,name,money)
select * from emp t;
2、有条件INSERT
insert all
when id in ('1','2') then --满足此条件插入emp1
into emp1(id,name,sal) values (id,name,sal)
when id in ('3') then --满足此条件插入emp2
into emp2(id,name,money) values (id,name,money)
select * from emp;
insert all
when id in ('1','2') then --满足此条件插入emp1
into emp1(id,name,sal) values (id,name,sal)
--这里不加条件的话此处条件同 emp1插入条件
into emp2(id,name,money) values (id,name,money)
select * from emp;
insert all
--这里无插入条件而emp2有插入条件,会报错
into emp1(id,name,sal) values (id,name,sal)
when id in ('3') then --满足此条件插入emp2
into emp2(id,name,money) values (id,name,money)
select * from emp;
3、有条件INSERT FIRST
第一个表符合条件后,第二张表不会插入对应的行;如下,第二张表仅会插入id='3'的数据
insert FIRST
when id in ('1','2') then
into emp1(id,name,sal) values (id,name,sal)
when id in ('1','2','3') then
into emp2(id,name,money) values (id,name,money)
select * from emp