Oracle应用之insert all用法简介

Oracle应用之insert all用法简介

insert all是oracle中用于批量写数据的

现在直接通过例子学习一下，比较简单直观，例子来自《收获，不止SQL优化》一书

环境准备

create table t as select object_name,rownum as object_idfrom 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_frifrom sales_source_data;commit;

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。