简介
insert all将同一批数据插入到符合条件的若干表当中。支持无条件插入，条件插入，行转列插入。Insert first 将同一批数据插入到第一个符合条件的表中。
|
语法
* WHEN…THEN… ：支持多次出现，when…then…组合使用时，当表达式第一个条件为 true 之后，insert first 会跳过后面的条件并结束插入，而insert all 会继续执行接下来若干条件为 true 的操作。
* set environment sqlmode ‘oracle’
举例
注意：以下所用示例使用的数据库版本为:GBase8sV8.8_TL_3.6.1_x86_64
数据准备
#数据准备 | |
create table testab(id int,name varchar(20),sex varchar(20),age int); | |
#向test1表插入数据 | |
insert into testab values(101,'lisi','female',18); | |
insert into testab values(102,'lisi','female',18); | |
insert into testab values(103,'xiaowu','male',19); | |
#复制表结构创建两张测试表 | |
create table tab1 as select * from testab where 1 = 2; | |
create table tab2 as select * from testab where 1 = 2; | |
#查询表结构 | |
select * from testab; | |
> select * from testab; id name sex age 101 lisi female 18 102 lisi female 18 103 xiaowu male 19 3 row(s) retrieved. > select * from tab1; id name sex age No rows found. > select * from tab2; id name sex age No rows found. |
Insert all 无条件插入（不带when）
#将testab的数据插入tab1,tab2中 [插入次数取决于testab的数据] | |
insert all into tab1 values(id,name,sex,age) into tab2 values(id,name,sex,age) select * from testab; | |
> insert all into tab1 values(id,name,sex,age) into tab2 values(id,name,sex,age) select * from testab; 6 row(s) inserted. Elapsed time: 0.034 sec >Select * from testab; ID NAME SEX AGE 101 lisi female 18 102 lisi female 18 103 xiaowu male 19 3 row(s) retrieved. Elapsed time: 0.052 sec >Select * from tab1; ID NAME SEX AGE 101 lisi female 18 102 lisi female 18 103 xiaowu male 19 3 row(s) retrieved. Elapsed time: 0.044 sec > Select * from tab2; ID NAME SEX AGE 101 lisi female 18 102 lisi female 18 103 xiaowu male 19 3 row(s) retrieved. Elapsed time: 0.052 sec | |
#删除表数据 Delete from tab1; Delete from tab2; | |
#同时给多个表插入数据，[仅插入一次] | |
> insert all into tab1 values(111,'xiaowang','female',18) into tab2 values(111,'xiaowang','female',18) select 1 from dual; | |
2 row(s) inserted. Elapsed time: 0.035 sec | |
> select * from tab1; ID NAME SEX AGE 111 xiaowang female 18 1 row(s) retrieved. Elapsed time: 0.052 sec > select * from tab2; ID NAME SEX AGE 111 xiaowang female 18 1 row(s) retrieved. Elapsed time: 0.053 sec | |
#删除表数据 Delete from tab1; Delete from tab2; | |
Insert all 条件插入
条件插入又分为两种：insert all when…和insert first when…
insert all when类型：对 '每个' 匹配成功项都进行插入
#向tab1,tab2条件插入testab的数据 | |
>insert all when id=101 then into tab1 values(id,name,sex,age) when id=102 then into tab2 values(id,name,sex,age) else into tab1 values(id,name,sex,age) select id,name,sex,age from testab; 3 row(s) inserted. Elapsed time: 0.034 sec | |
#id=101，103数据存入tab1中，id=2数据存入tab2中 | |
> select * from tab1; ID NAME SEX AGE 101 lisi female 18 103 xiaowu male 19 2 row(s) retrieved. Elapsed time: 0.052 sec > select * from tab2; ID NAME SEX AGE 102 lisi female 18 1 row(s) retrieved. Elapsed time: 0.052 sec | |
#删除表数据 | |
delete from tab1; | |
delete from tab2; | |
insert all first类型:仅对 '第一个' 匹配成功项进行插入
#向tab1,tab2条件插入testab的数据 | |
#第一个条件匹配向tab1插入数据，第二个条件满足的时候跳过，避免重复插入的问题。 >insert first when id=101 then into tab1 values(id,name,sex,age) when id=101 then into tab2 values(id,name,sex,age) select id,name,sex,age from testab; 1 row(s) retrieved. Elapsed time: 0.052 sec | |
#tab1中插入数据，tab2为空 > select * from tab1; ID NAME SEX AGE 101 lisi female 18 1 row(s) retrieved. Elapsed time: 0.052 sec > select * from tab2; ID NAME SEX AGE No rows found. Elapsed time: 0.048 sec | |
行转列插入 (注意数据类型一致)
#数据准备 | |
create table testab1(id int,name varchar(20),wagemon float,wagetue float,wagewed float); | |
Insert into testab1 values(111,'xiaowang','1.1','1.2','1.3'); | |
> Select * from testab1; id name wagemon wagetue wagewed 111 xiaowang 1.100000000000 1.200000000000 1.300000000000 1 row(s) retrieved. Elapsed time: 0.052 sec | |
Create table testmp(id int,name varchar(20),wage float); | |
#insert all 将testab1中的wagemon，wagetue，wagewed数据存入testmp的wage中 | |
insert all into testmp values(id,name,wagemon) into testmp values(id,name,wagetue) into testmp values(id,name,wagewed) select * from testab1; | |
> insert all into testmp values(id,name,wagemon) into testmp values(id,name,wagetue) into testmp values(id,name,wagewed) select * from testab1;> > > > 3 row(s) inserted. Elapsed time: 0.036 sec > select * from testmp; ID NAME WAGE 111 xiaowang 1.100000000000 111 xiaowang 1.200000000000 111 xiaowang 1.300000000000 3 row(s) retrieved. Elapsed time: 0.052 sec | |
多表插入语句的限制条件
- 只能对表执行多表插入语句，不能对视图或物化视图执行；
- 不能对远端表执行多表插入语句；
- 不能使用表集合表达式；
- 不能超过999个目标列；
- 在RAC环境中或目标表是索引组织表或目标表上建有BITMAP索引时，多表插入语句不能并行执行；
- 多表插入语句不支持执行计划稳定性；
- 多表插入语句中的子查询不能使用序列。
以上为insert all使用的简单介绍，具体可以参考《GBase 8s V8.8 SQL 指南：语法.pdf》
