Oracle 数据仓库 ETL 技术之多表插入语句

文章目录

        创建示例表
        无条件的 INSERT ALL 语句
        有条件的 INSERT ALL 语句
        有条件的 INSERT FIRST 语句
        多表插入语句的限制

ETL（提取、转换、加载）是指从源系统中提取数据并将其放入数据仓库的过程。Oracle 数据库为 ETL 流程提供了丰富的功能，今天我们就给大家介绍一下 Oracle 多表插入语句，也就是 INSERT ALL 语句。

如果觉得文章有用，欢迎评论📝、点赞👍、推荐🎁
创建示例表

我们首先创建一个源数据表和三个目标表：

CREATE TABLE src_table(
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR2(10) NOT NULL
);
INSERT INTO src_table VALUES (1, '张三');
INSERT INTO src_table VALUES (2, '李四');
INSERT INTO src_table VALUES (3, '王五');

CREATE TABLE tgt_t1 AS
SELECT * FROM src_table WHERE 1=0;

CREATE TABLE tgt_t2 AS
SELECT * FROM src_table WHERE 1=0;

CREATE TABLE tgt_t3 AS
SELECT * FROM src_table WHERE 1=0;

 

无条件的 INSERT ALL 语句

INSERT ALL 语句可以用于将多行输入插入一个或者多个表中，因此也被称为多表插入语句。第一种形式的 INSERT ALL 语句是无条件的插入语句，源数据中的每一行数据都会被插入到每个目标表中。例如：

INSERT ALL
  INTO tgt_t1(id, name) VALUES(id, name)
  INTO tgt_t2(id, name) VALUES(id, name)
  INTO tgt_t3(id, name) VALUES(id, name)
SELECT * FROM src_table;

SELECT * FROM tgt_t1;
ID|NAME  |
--|------|
 1|张三  |
 2|李四  |
 3|王五  |

SELECT * FROM tgt_t2;
ID|NAME  |
--|------|
 1|张三  |
 2|李四  |
 3|王五  |

SELECT * FROM tgt_t3;
ID|NAME  |
--|------|
 1|张三  |
 2|李四  |
 3|王五  |

执行以上多表插入语句之后，三个目标表中都生成了 3 条记录。

我们也可以多次插入相同的表，实现一个插入语句插入多行数据的效果。例如：

TRUNCATE TABLE tgt_t1;

INSERT ALL
  INTO tgt_t1(id, name) VALUES(4, '赵六')
  INTO tgt_t1(id, name) VALUES(5, '孙七')
  INTO tgt_t1(id, name) VALUES(6, '周八')
SELECT 1 FROM dual;

SELECT * FROM tgt_t1;
ID|NAME  |
--|------|
 4|赵六  |
 5|孙七  |
 6|周八  |

   
在以上插入语句中，tgt_t1 出现了三次，最终在该表中插入了 3 条记录。这种语法和其他数据库中的以下多行插入语句效果相同：

-- MySQL、SQL Server、PostgreSQL以及SQLite
INSERT INTO tgt_t1(id, name)
VALUES(4, '赵六'), (5, '孙七'), (6, '周八');

   
另外，这种无条件的 INSERT ALL 语句还可以实现列转行（PIVOT）的功能。例如：

更多请见：http://www.mark-to-win.com/tutorial/51645.html