在数据仓库中的转换和装载过程中，可能会使用INSERT ALL语句，这篇文章简单介绍一下INSERT ALL语句。但在ETL工具中很多组件也有类似的功能，实现逻辑雷同。
虽然INSERT ALL是9i新增的语法，它扩充了原有的INSERT语句，使得INSERT语句从原来的只能插入到一张表发展到可以同时插入多张表，还可以根据判断条件来决定每条记录插入到哪张或哪几张表中。
1、不带条件的INSERT ALL用法：
SQL> CREATE TABLE TABLE_STORAGE
2 (
3 TABLE_NAME VARCHAR2(30),
4 TABLESPACE_NAME VARCHAR2(30),
5 PCT_FREE NUMBER,
6 PCT_USED NUMBER,
7 INI_TRANS NUMBER,
8 MAX_TRANS NUMBER,
9 INITIAL_EXTENT NUMBER,
10 NEXT_EXTENT NUMBER,
11 MIN_EXTENTS NUMBER,
12 MAX_EXTENTS NUMBER,
13 PCT_INCREASE NUMBER,
14 FREELISTS NUMBER,
15 FREELIST_GROUPS NUMBER
16 );
表已创建。
SQL> CREATE TABLE TABLE_STAT
2 (
3 TABLE_NAME VARCHAR2(30),
4 NUM_ROWS NUMBER,
5 BLOCKS NUMBER,
6 EMPTY_BLOCKS NUMBER,
7 AVG_SPACE NUMBER,
8 CHAIN_CNT NUMBER,
9 AVG_ROW_LEN NUMBER
10 );
表已创建。
SQL> INSERT ALL
2 INTO TABLE_STORAGE VALUES (TABLE_NAME, TABLESPACE_NAME, PCT_FREE, PCT_USED,
3 INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS,PCT_INCREASE, FREELISTS, FREELIST_GROUPS)
4 INTO TABLE_STAT VALUES (TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE,CHAIN_CNT, AVG_ROW_LEN)
5 SELECT * FROM USER_TABLES;
已创建54行。
SQL> SELECT COUNT(*) FROM TABLE_STORAGE;
COUNT(*)
----------
12
SQL> SELECT COUNT(*) FROM TABLE_STAT;
COUNT(*)
----------
12
SQL> SELECT COUNT(*) FROM USER_TABLES;
COUNT(*)
----------
12
SQL> DROP TABLE TABLE_STAT;
表已丢弃。
SQL> DROP TABLE TABLE_STORAGE;
表已丢弃。
2、带条件的INSERT ALL用法：
SQL> CREATE TABLE TABLE_ALL (TABLE_NAME VARCHAR2(30));
表已创建。
SQL> CREATE TABLE INDEX_ALL (INDEX_NAME VARCHAR2(30));
表已创建。
SQL> CREATE TABLE OBJECT_OTHER (OBJECT_NAME VARCHAR2(30), OBJECT_TYPE VARCHAR2(30));
表已创建。
SQL> INSERT ALL
2 WHEN (object_type = 'TABLE') THEN INTO table_all VALUES (object_name)
3 WHEN (object_type = 'INDEX') THEN INTO index_all VALUES (object_name)
4 ELSE INTO object_other (OBJECT_NAME, OBJECT_TYPE)
5 SELECT object_name, object_type FROM user_objects;
已创建91行。
SQL> SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE';
COUNT(*)
----------
27
SQL> SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_TYPE = 'INDEX';
COUNT(*)
----------
14
SQL> SELECT COUNT(*) FROM USER_OBJECTS
2 WHERE OBJECT_TYPE NOT IN ('TABLE', 'INDEX');
COUNT(*)
----------
50
SQL> SELECT COUNT(*) FROM TABLE_ALL;
COUNT(*)
----------
27
SQL> SELECT COUNT(*) FROM INDEX_ALL;
COUNT(*)
----------
14
SQL> SELECT COUNT(*) FROM OBJECT_OTHER;
COUNT(*)
----------
50
SQL> DROP TABLE TABLE_ALL;
表已丢弃。
SQL> DROP TABLE INDEX_ALL;
表已丢弃。
SQL> DROP TABLE OBJECT_OTHER;
表已丢弃。
3、INSERT ALL和INSERT FIRST的区别：
/*
对于INSERT ALL，插入第一张表的数据，如果满足第二张表的条件，也会插入。
而INSERT FIRST不同，满足第一条插入条件的数据是不会进行随后的判断的，所以在INSERT FIRST执行后，在TABLESPACE_USERS表中看不到SEGMENT_TYPE为TABLE的记录
*/
SQL> CREATE TABLE TABLESPACE_USERS (NAME VARCHAR2(30), TYPE VARCHAR2(30));
表已创建。
SQL> CREATE TABLE TABLE_ALL (TABLE_NAME VARCHAR2(30), TYPE VARCHAR2(30));
表已创建。
SQL> INSERT ALL
2 WHEN (segment_type = 'TABLE') THEN INTO table_all VALUES (segment_name, segment_type)
3 WHEN (tablespace_name = 'USERS') THEN INTO tablespace_users VALUES (segment_name, segment_type)
4 SELECT segment_name, segment_type, tablespace_name FROM user_segments;
已创建69行。
SQL> SELECT COUNT(*) FROM TABLE_ALL;
COUNT(*)
----------
21
SQL> SELECT COUNT(*) FROM TABLESPACE_USERS;
COUNT(*)
----------
48
SQL> SELECT COUNT(*) FROM TABLESPACE_USERS WHERE TYPE = 'TABLE';
COUNT(*)
----------
18
SQL> TRUNCATE TABLE TABLE_ALL;
表已截掉。
SQL> TRUNCATE TABLE TABLESPACE_USERS;
表已截掉。
SQL> INSERT FIRST
2 WHEN (segment_type = 'TABLE') THEN INTO table_all VALUES (segment_name, segment_type)
3 WHEN (tablespace_name = 'USERS') THEN INTO tablespace_users VALUES (segment_name, segment_type)
4 SELECT segment_name, segment_type, tablespace_name FROM user_segments;
已创建51行。
SQL> SELECT COUNT(*) FROM TABLE_ALL;
COUNT(*)
----------
21
SQL> SELECT COUNT(*) FROM TABLESPACE_USERS;
COUNT(*)
----------
30
SQL> SELECT COUNT(*) FROM TABLESPACE_USERS WHERE TYPE = 'TABLE';
COUNT(*)
----------
0
最后看一下多表插入语句的限制条件：
1、只能对表执行多表插入语句，不能对视图或物化视图执行；
2、不能对远端表执行多表插入语句；
3、不能使用表集合表达式；
4、不能超过999个目标列；
5、在RAC环境中或目标表是索引组织表或目标表上建有BITMAP索引时，多表插入语句不能并行执行；
6、多表插入语句不支持执行计划稳定性；
7、多表插入语句中的子查询不能使用序列
