Oracle中 insert all 语句用法详解

在工作中，有时会遇到这样的需求：将数据插入多个表中。

怎么实现呢？

方法1：使用insert into语句分别插入，效率较低。
方法2：使用insert all语句批量插入数据，效率较高。（insert all又分为无条件插入和有条件插入）

一、表和数据准备

--创建表
SQL> CREATE TABLE test1(
   ID   NUMBER(3),
   NAME VARCHAR2(20),
   SEX  VARCHAR2(2),
   AGE  NUMBER(3)
);
 

--向test1表中插入数据
SQL> INSERT INTO test1(ID, NAME, SEX, AGE) VALUES(1, '张三', '女', '21');
SQL> INSERT INTO test1(ID, NAME, SEX, AGE) VALUES(2, '李四', '男', '25');
SQL> INSERT INTO test1(ID, NAME, SEX, AGE) VALUES(3, '王五', '女', '22');
SQL> commit;

--复制表结构创建表test2，test3
SQL> CREATE TABLE test2 AS SELECT t.* FROM test1 t WHERE 1 = 2;
SQL> CREATE TABLE test3 AS SELECT t.* FROM test1 t WHERE 1 = 2;

--查询表
SQL>  select * from test1;

        ID NAME                 SEX        AGE
---------- -------------------- --- ----------
         1 张三                 女         21
         2 李四                 男         25
         3 王五                 男         22

SQL> select * from test2;

no rows selected

SQL> select * from test3;

no rows selected
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

二、insert all 无条件插入

--将test1表中的数据插入test2和test3表中
SQL> insert all
     into test2 values(id,name,sex,age)
	 into test3 values(id,name,sex,age)
	 select id,name,sex,age from test1;
SQL> commit;
1
2
3
4
5
6

--查询表
SQL> select * from test2;

        ID NAME                 SEX        AGE
---------- -------------------- --- ----------
         1 张三                 女         21
         2 李四                 男         25
         3 王五                 男         22


SQL> select * from test3;

        ID NAME                 SEX        AGE
---------- -------------------- --- ----------
         1 张三                 女         21
         2 李四                 男         25
         3 王五                 男         22

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

同时向多张表插入新数据也可以通过如下语句：

--同时向3张表中插入单条相同新数据
SQL> insert all
     into test1 values(4,'赵六','女','19')
     into test2 values(4,'赵六','女','19')
     into test3 values(4,'赵六','女','19')
     select 1 from dual;
SQL> commit;

--查询表
SQL> select * from test1;

        ID NAME                 SEX        AGE
---------- -------------------- --- ----------
         4 赵六                 女         19
         1 张三                 女         21
         2 李四                 男         25
         3 王五                 男         22

SQL> select * from test2;

        ID NAME                 SEX        AGE
---------- -------------------- --- ----------
         1 张三                 女         21
         2 李四                 男         25
         3 王五                 男         22
         4 赵六                 女         19

SQL> select * from test3;

        ID NAME                 SEX        AGE
---------- -------------------- --- ----------
         1 张三                 女         21
         2 李四                 男         25
         3 王五                 男         22
         4 赵六                 女         19
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

--先删除表中数据
 SQL>  delete from test1;
 SQL>  delete from test2;
 SQL>  delete from test3;
 SQL> commit;
  
--同时向多张表中插入多条不同数据语句
 SQL> insert all
      into test1 values(1,'张三','女','21')
      into test1 values(2, '李四', '男', '25')
      into test2 values(3, '王五', '女', '22')
      into test3 values(4,'赵六','女','19')
      into test3 values(5,'孙七','女','23')
      select 1 from dual;
SQL> commit;

--查询表
SQL> select * from test1;

        ID NAME                 SEX        AGE
---------- -------------------- --- ----------
         1 张三                 女         21
         2 李四                 男         25
         
SQL> select * from test2;

        ID NAME                 SEX        AGE
---------- -------------------- --- ----------
         3 王五                 男         22

SQL> select * from test3;

        ID NAME                 SEX        AGE
---------- -------------------- --- ----------
         4 赵六                 女          19
         5 孙七                 女          23
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

三、insert all 有条件插入

有条件插入又分为两种：insert all when…和insert first when…

insert all when类型：

--删除表中数据
SQL> delete from test1;
SQL> delete from test2;
SQL> delete from test3;
SQL> commit;

--重新向test1表插入数据
SQL> commit;INSERT INTO test1(ID, NAME, SEX, AGE) VALUES(1,'赵六','女','19');
SQL> commit;INSERT INTO test1(ID, NAME, SEX, AGE) VALUES(2,'李四','男','25');
SQL> commit;INSERT INTO test1(ID, NAME, SEX, AGE) VALUES(3,'王五','女','22');
SQL> commit;INSERT INTO test1(ID, NAME, SEX, AGE) VALUES(4,'赵六','女','19');
SQL> commit;

--向test2和test3表中有条件插入数据
SQL> insert all
    when id=1 then
	  into test2 values(id,name,sex,age)
	when id=2 then 
      into test3 values(id,name,sex,age)
    else
	  into test2 values(id,name,sex,age)
	select id,name,sex,age from test1;
SQL> commit;

--查询表
SQL> select * from test1;

        ID NAME                 SEX        AGE
---------- -------------------- --- ----------
         1 张三                 女         21
         2 李四                 男         25
         3 王五                 男         22
         4 赵六                 女         19

SQL> select * from test2;

        ID NAME                 SEX        AGE
---------- -------------------- --- ----------
         1 张三                 女         21
         3 王五                 男         22
         4 赵六                 女         19

SQL> select * from test3;

        ID NAME                 SEX        AGE
---------- -------------------- --- ----------
         2 李四                 男         25
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

insert first when 类型：

--删除test2和test3表数据
SQL> delete from test2;
SQL> delete from test3;
SQL> commit;

--查询表
SQL> select * from test2;

        ID NAME                 SEX        AGE
---------- -------------------- --- ----------
         1 张三                 女         21
         2 李四                 男         25

SQL> select * from test3;

        ID NAME                 SEX        AGE
---------- -------------------- --- ----------
         3 王五                 男         22
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

insert all when 和 insert first when 的区别:

insert first是考虑先后关系的，如果有数据满足第一个when条件又满足第二个when条件，则执行第一个then插入语句，第二个then就不插入。反之有数据不满足第一个when条件且满足第二个when条件,则数据会插入第二个条件下对应的表中，这也正是insert first与inset all的区别。

简单来说就是insert all when只要满足条件，就会插入，这个会造成重复插入；insert first when只要有一个满足条件，后面的条件不再判断，不会造成重复插入。

注意：insert all 无法支持序列插入，会导致两边不一致。

---

参考文章：
https://www.cnblogs.com/jasonboren/p/12102490.html
https://www.cnblogs.com/masha2017/p/11413747.html