Oracle應用之insert all用法簡介

insert all是oracle中用于批量寫資料的

現在直接通過例子學習一下，比較簡單直覺，例子來自《收獲，不止SQL優化》一書

環境準備

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

複制

建立兩張測試表，不用寫資料

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

複制

然後示範一下insert all的用法

無條件寫資料的情況

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

複制

有條件寫資料的情況

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情況，介紹一下insert first的用法，insert first用法和insert all類似，差別的是insert first多了篩選的步驟，簡單來說就是和insert all一樣，符合條件的同樣會寫資料，不過已經存在資料了，insert first是不會寫入的，而insert all是會出現重複資料的情況

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

然後再示範一下pivoting insert的情況，pivoting insert可以說是insert all的一直特殊情況，不過oracle官方還是區分出來，pivoting insert可以翻譯為旋轉寫入，名稱的不重要，看一下例子就懂了

環境準備

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)
);           

複制

按照條件進行寫資料

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;           

複制