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 数据只会插入一个表。

KingbaseES insert all/first 功能介绍的相关教程结束。