insert all is used to write data in batch in oracle

Now let's directly learn from the example, which is relatively simple and intuitive. The example comes from the book harvest, not only SQL optimization.

Environmental preparation

create table t as select object_name,rownum as object_id
from dba_objects where rownum<=10;

Create two test tables without writing data

create table t1 as select * from t where 1=2;
create table t2 as select * from t where 1=2;

Then demonstrate the use of insert all

Write data unconditionally

insert all into t1
  (object_name, object_id) into t2
  (object_name, object_id)
  select * from t;
commit;

Conditional data writing

truncate table t1;
truncate table t2;
insert all when object_id < 5 then into t1
  (object_name, object_id) when object_id >= 5 then into t2
  (object_name, object_id)
  select * from t;
commit;

insert first
Insert first is similar to insert all. The difference is that insert first has more filtering steps. Simply speaking, it can write data as well as insert all. But there is already data, insert first will not write, and insert all will have duplicate data.

truncate table t1;
truncate table t2;
insert first when object_id = 1 then into t1
  (object_name, object_id) when object_id <= 5 then into t2
  (object_name, object_id)
  select * from t;
commit;

pivoting insert
Then I will demonstrate the situation of pivoting insert. It can be said that pivoting insert has always been a special case of insert all. However, oracle officially distinguishes it. Pivoting insert can be translated into rotation write. The name is not important. Take a look at the example and you will see.

Environmental preparation

drop table sales_source_data;
create table sales_source_data(
employee_id number(10),
week_id number(2),
sales_mon number(8,2),
sales_tue number(8,2),
sales_wed number(8,2),
sales_thur number(8,2),
sales_fri number(8,2)
);
insert into sales_source_data values(280,6,2000,3000,4000,5000,6000);
commit;
create table sales_info(
employee_id number(10),
week number(2),
sales number(8,2)
);

Write data according to conditions

insert all 
into sales_info values(employee_id,week_id,sales_mon)
into sales_info values(employee_id,week_id,sales_tue)
into sales_info values(employee_id,week_id,sales_wed)
into sales_info values(employee_id,week_id,sales_thur)
into sales_info values(employee_id,week_id,sales_fri)
select employee_id,week_id,sales_mon,sales_tue,
sales_wed,sales_thur,sales_fri
from sales_source_data;
commit;