Oracle insert all用法简介
2024-10-02 02:13:24
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;
最新文章
- dicom网络通讯入门(1)
- 火狐浏览器URL中传中文参数乱码问题
- Centos minimal安装
- 整合Servlet到Spring容器
- MRP运算生成采购单时间的逻辑
- Linux下如何修改ip地址
- 第四章 跨平台图像显示库——SDL 第一节 与SDL第一次亲密接触
- 查看某个html标签有哪些属性和事件
- 【C++基础】 类中static private public protected
- Centos硬件信息查看命令
- ExtJS4.2 Ext.grid.panel Store更改后刷新表格
- xcode 不值钱的动画UIButton
- IBM的人工智能“沃森”首次确诊罕见白血病,只用了10分钟!
- CSS 样式书写规范
- 处理php出现default timezone抖动的问题
- 个人作业-Week1(新增详细说明)
- RHEL7和RHEL6即时设置、开启和开机、永久开启服务的方法、原理(例子:端口与Nginx冲突的Apache httpd服务的关闭)
- how2j网站前端项目——天猫前端(第一次)学习笔记1
- 纯html上下翻滚效果公告板
- [leetcode]Construct Binary Tree from Preorder and Inorder Traversal @ Python