在工作中,有时会遇到这样的需求:将数据插入多个表中。
怎么实现呢?
方法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
二、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;
--查询表
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
同时向多张表插入新数据也可以通过如下语句:
--同时向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
--先删除表中数据
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
三、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
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
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