Oracle SQL 多表插入（INSERT）

INSERT语句多表插入。简而言之：用一条插入语句将记录插入到多个表中。

创建表

CREATE TABLE test_1(
part_id number,
part_char varchar2(30)
);
INSERT INTO test_1 VALUES(100, 'a');
INSERT INTO test_1 VALUES(200, 'b');
INSERT INTO test_1 VALUES(300, 'c');
INSERT INTO test_1 VALUES(400, 'd');

CREATE TABLE test_2 as
SELECT * FROM test_1
WHERE 1=2;

CREATE TABLE test_3 as
SELECT * FROM test_1
WHERE 1=2;

test_1表中有4条数据，test_2和test_3表中没有数据。

第一种插入：

/*全表插入*/
INSERT ALL
INTO test_2 VALUES(part_id, part_char)
INTO test_3 VALUES(part_id, part_char)
SELECT part_id, part_char FROM test_1;
执行过后会将test_1表中的4条数据分别插入test_2和test_3表中。

/*加条件插入*/
DELETE FROM test_2;
DELETE FROM test_3;
INSERT ALL
WHEN part_id<250 THEN INTO test_2 VALUES(part_id, part_char)
WHEN part_id>250 THEN INTO test_3 VALUES(part_id, part_char)
SELECT part_id, part_char FROM tset_1;
执行过后会将test_1表中<250的数据插入test_2，>250的插入test_3。

INSERT ALL
WHEN part_id<150 THEN INTO test_2 VALUES(part_id, part_char)
ELSE INTO test_3 VALUES(part_id, part_char)
SELECT part_id, part_char FROM test_1;
和上面差不多。

第二种插入：

DELETE FROM test_2;
DELETE FROM test_3;
INSERT FIRST
WHEN part_id>300 THEN INTO test_2 VALUES(part_id, part_char)
WHEN part_id>100 THEN INTO test_3 VALUES(part_id, part_char)
SELECT part_id, part_char FROM test_1;

SQL> SELECT * FROM test_1;

   PART_ID PART_CHAR
---------- ------------------------------
       100 a
       200 b
       300 c
       400 d

SQL> SELECT * FROM test_2;

   PART_ID PART_CHAR
---------- ------------------------------
       400 d

SQL> SELECT * FROM test_3;

   PART_ID PART_CHAR
---------- ------------------------------
       200 b
       300 c

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

--Over--