oracle 多个insert c,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--