KingbaseES insert all/first 功能介绍

KingbaseES 内置了对于insert all / first 语法的支持。

一、数据准备

create table t1(product_id number, product_name varchar2(80),month number);  
create table t2(product_id number, product_name varchar2(80),month number);  
create table t3(product_id number, product_name varchar2(80),month number);  
create table t4(product_id number, product_name varchar2(80),month number);  
create table t5(product_id number, product_name varchar2(80),month number);  

insert into t1 values(111, 'apple',1);  
insert into t1 values(222, 'orange',1);  
insert into t1 values(333, 'banana',1);  

insert all  
    into t2  values (product_id, product_name,month)  
    into t2  values (product_id, product_name,month+1)  
    into t2  values (product_id, product_name,month+2)  
    into t2  values (product_id, product_name,month+3)   
select product_id, product_name, month from t1;

test=# select * from t1;
 product_id | product_name | month 
------------+--------------+-------
        111 | apple        |     1
        222 | orange       |     1
        333 | banana       |     1
(3 rows)

test=# select * from t2;
 product_id | product_name | month 
------------+--------------+-------
        111 | apple        |     1
        222 | orange       |     1
        333 | banana       |     1
        111 | apple        |     2
        222 | orange       |     2
        333 | banana       |     2
        111 | apple        |     3
        222 | orange       |     3
        333 | banana       |     3
        111 | apple        |     4
        222 | orange       |     4
        333 | banana       |     4
(12 rows)

二、insert all

insert all 
when month=1 then into t3
when month=2 then into t4
else into t5
select product_id,product_name,month from t2;

test=# select * from t3;
 product_id | product_name | month 
------------+--------------+-------
        111 | apple        |     1
        222 | orange       |     1
        333 | banana       |     1
(3 rows)

test=# select * from t4;
 product_id | product_name | month 
------------+--------------+-------
        111 | apple        |     2
        222 | orange       |     2
        333 | banana       |     2
(3 rows)

test=# select * from t5;
 product_id | product_name | month 
------------+--------------+-------
        111 | apple        |     3
        222 | orange       |     3
        333 | banana       |     3
        111 | apple        |     4
        222 | orange       |     4
        333 | banana       |     4
(6 rows)

三、insert first

truncate table t3;
truncate table t4;
truncate table t5;

insert first 
when month<2 then into t3 values(product_id,product_name,month)  
when month<3 then into t4 values(product_id,product_name,month)  
else into t5 values(product_id,product_name,month)  
select product_id,product_name,month from t2;

test=# select * from t3;
 product_id | product_name | month 
------------+--------------+-------
        111 | apple        |     1
        222 | orange       |     1
        333 | banana       |     1
(3 rows)

test=# select * from t4;
 product_id | product_name | month 
------------+--------------+-------
        111 | apple        |     2
        222 | orange       |     2
        333 | banana       |     2
(3 rows)

test=# select * from t5;
 product_id | product_name | month 
------------+--------------+-------
        111 | apple        |     3
        222 | orange       |     3
        333 | banana       |     3
        111 | apple        |     4
        222 | orange       |     4
        333 | banana       |     4
(6 rows)

四、总结

insert first 当数据满足第一when 条件的时候，不会作用在下面的when条件了，简而言之:当一条数据满足条件后，即使满足后面的条件也不会插入数据库。如果数据满足所有的when条件，insert all每个表都会插入该条数据，insert first 数据只会插入一个表。