SQL> create table test_1
  2  (id number,
  3  name varchar(5));

Table created.

SQL> insert into test_1 values(100,'a');

1 row created.

SQL> insert into test_1 values(200,'b');

1 row created.

SQL> insert into test_1 values(300,'c');

1 row created.

SQL> insert into test_1 values(400,'d');

1 row created.

SQL> commit;

Commit complete.

SQL> create table test_2 as select * from test_1 where 1=2;

Table created.

SQL> create table test_3 as select * from test_1 where 1=2;

Table created.

SQL> select * from test_1;

        ID NAME
---------- -----
       100 a
       200 b
       300 c
       400 d

SQL> select * from test_2;

no rows selected

SQL> select * from test_3;

no rows selected

SQL>

test_1表有四条数据,test_2,test_3表没有记录

第一种插入：无条件insert all

  全表插入

 SQL> insert all
  2  into test_2 values(id,name)
  3  into test_3 values(id,name)
  4  select id,name from test_1;

8 rows created.

SQL> select * from test_2;

        ID NAME
---------- -----
       100 a
       200 b
       300 c
       400 d

SQL> select * from test_3;

        ID NAME
---------- -----
       100 a
       200 b
       300 c
       400 d

第二种:有条件insert all

SQL> insert all
  2  when id<250 then
  3  into test_2 values(id,name)
  4  when id>250 then
  5  into test_3 values(id,name)
  6  select id,name from test_1;

4 rows created.

SQL> select *from test_2;

        ID NAME
---------- -----
       100 a
       200 b

SQL> select *from test_3;

        ID NAME
---------- -----
       300 c
       400 d

执行过后将id<250的插入test_2,id>250的插入test_3

SQL> insert all
  2  when id<150 then
  3  into test_2 values(id,name)
  4  else
  5  into test_3 values(id,name)
  6  select id,name from test_1;

4 rows created.

SQL> select *from test_2;

        ID NAME
---------- -----
       100 a

SQL> select *from test_3;

        ID NAME
---------- -----
       200 b
       300 c
       400 d

第三种:有条件insert first

SQL> insert first
  2  when id>300 then
  3  into test_2 values(id,name)
  4  when id>100 then
  5  into test_3 values(id,name)
  6  select id,name from test_1;

3 rows created.

SQL> select * from test_2;

        ID NAME
---------- -----
       400 d

SQL> select * from test_3;

        ID NAME
---------- -----
       200 b
       300 c

从结果中可以看出，此种插入源表中的每条记录只会被插入一次.test_1表中大于300的数据只有一条，即id=400的记录，当满足id>100，由于此条记录已插入到test_2中，所以在test_3表中不再被插入。