oracle多表插入

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表中不再被插入。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26937943/viewspace-1302944/，如需轉載，請註明出處，否則將追究法律責任。