Oracle Data Warehouse ETL Technology multi table insert statement example explanation

Time:2022-4-27
catalogue
  • Create sample table
  • Unconditional insert all statement
  • Conditional insert all statement
  • Conditional insert first statement
  • Restrictions on multi table insert statements

dwh

hello everyone! I’m a teacher Tony who only talks about technology and doesn’t cut his hair.

ETL (extract, transform, load) refers to the process of extracting data from the source system and putting it into the data warehouse. Oracle database provides rich functions for ETL process. Today, we will introduce Oracle multi table insert statement, that is, insert all statement.

Create sample table

We first create a source data table and three target tables:

CREATE TABLE src_table(
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR2(10) NOT NULL
);
INSERT INTO src_ Table values (1, 'Zhang San');
INSERT INTO src_ Table values (2, 'Li Si');
INSERT INTO src_ Table values (3, 'Wang Wu');

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;

Unconditional insert all statement

Insert all statement can be used to insert multi row input into one or more tables, so it is also called multi table insert statement. First form INSERT ALL Statement is an unconditional insert statement. Each row of data in the source data will be inserted into each target table. For example:

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 | Zhang San|
 Li Si|
 3. Wang Wu|

SELECT * FROM tgt_t2;
ID|NAME  |
--|------|
 1 | Zhang San|
 Li Si|
 3. Wang Wu|

SELECT * FROM tgt_t3;
ID|NAME  |
--|------|
 1 | Zhang San|
 Li Si|
 3. Wang Wu|

After executing the above multi table insert statement, three records are generated in the three target tables.

We can also insert the same table multiple times to achieve the effect of inserting multiple rows of data in one insert statement. For example:

TRUNCATE TABLE tgt_t1;

INSERT ALL
  INTO tgt_ T1 (ID, name) values (4, 'Zhao Liu')
  INTO tgt_ T1 (ID, name) values (5, 'sun Qi')
  INTO tgt_ T1 (ID, name) values (6, 'week 8')
SELECT 1 FROM dual;

SELECT * FROM tgt_t1;
ID|NAME  |
--|------|
 Zhao Liu|
 5. Sun Qi|
 June 8th|

In the above insert statement, TGT_ T1 appeared three times, and finally three records were inserted into the table. This syntax has the same effect as the following multi row insert statements in other databases:

--Mysql, SQL server, PostgreSQL and SQLite
INSERT INTO tgt_t1(id, name)
Values (4, 'Zhao Liu'), (5, 'sun Qi'), (6, 'Zhou Ba');

In addition, this unconditional insert all statement can also realize the function of column to row (pivot). For example:

CREATE TABLE src_pivot(
  id INTEGER NOT NULL PRIMARY KEY,
  name1 VARCHAR2(10) NOT NULL,
  name2 VARCHAR2(10) NOT NULL,
  name3 VARCHAR2(10) NOT NULL
);
INSERT INTO src_ Pivot values (1, 'Zhang San', 'Li Si', 'Wang Wu');

TRUNCATE TABLE tgt_t1;

INSERT ALL
  INTO tgt_t1(id, name) VALUES(id, name1)
  INTO tgt_t1(id, name) VALUES(id, name2)
  INTO tgt_t1(id, name) VALUES(id, name3)
SELECT * FROM src_pivot;

SELECT * FROM tgt_t1;
ID|NAME  |
--|------|
 1 | Zhang San|
 Li Si|
 1 | Wang Wu|

src_ The pivot table contains three name fields. We useINSERT ALL Statement to convert it into 3 lines of records.

Conditional insert all statement

The first form of insert all statement is a conditional insert statement, which can insert data satisfying different conditions into different tables. For example:

TRUNCATE TABLE tgt_t1;
TRUNCATE TABLE tgt_t2;
TRUNCATE TABLE tgt_t3;

INSERT ALL
  WHEN id <= 1 THEN
    INTO tgt_t1(id, name) VALUES(id, name)
  WHEN id BETWEEN 1 AND 2 THEN
    INTO tgt_t2(id, name) VALUES(id, name)
  ELSE
    INTO tgt_t3(id, name) VALUES(id, name)
SELECT * FROM src_table;

SELECT * FROM tgt_t1;
ID|NAME  |
--|------|
 1 | Zhang San|
 
SELECT * FROM tgt_t2;
ID|NAME  |
--|------|
 1 | Zhang San|
 Li Si|

SELECT * FROM tgt_t3;
ID|NAME  |
--|------|
 3. Wang Wu|

tgt_ One piece of data is inserted into T1 because there is only one record with ID less than or equal to 1. tgt_ Two pieces of data are inserted into T2, including records with ID equal to 1. In other words, the previous when clause will not affect the subsequent condition judgment, and each condition will be judged separately. tgt_ One piece of data is inserted into T3, and else branch will only insert data that does not meet all the previous conditions.

Conditional multi table insert statements support up to 127 when clauses.

Conditional insert first statement

The principle of conditional insert first is similar to that of case expression. It will only execute the first qualified insert statement, and then continue to process other records in the source data. For example:

TRUNCATE TABLE tgt_t1;
TRUNCATE TABLE tgt_t2;
TRUNCATE TABLE tgt_t3;

INSERT FIRST
  WHEN id <= 1 THEN
    INTO tgt_t1(id, name) VALUES(id, name)
  WHEN id BETWEEN 1 AND 2 THEN
    INTO tgt_t2(id, name) VALUES(id, name)
  ELSE
    INTO tgt_t3(id, name) VALUES(id, name)
SELECT * FROM src_table;

SELECT * FROM tgt_t1;
ID|NAME  |
--|------|
 1 | Zhang San|
 
SELECT * FROM tgt_t2;
ID|NAME  |
--|------|
 Li Si|

SELECT * FROM tgt_t3;
ID|NAME  |
--|------|
 3. Wang Wu|

The difference between the above statement and the previous example is that each record in the source data is inserted only once, TGT_ Data with ID equal to 1 will not be inserted in T2.

Restrictions on multi table insert statements

Oracle multi table insert statements have the following limitations:

  • Multi table insertion can only be performed for tables. Views or materialized views are not supported.
  • A multi table insert statement cannot perform an insert operation on a remote table through DB link.
  • Multiple table insert statements cannot perform insert operations on nested tables.
  • The total number of fields in all insert into clauses cannot exceed 999.
  • Sequences cannot be used in multi table insert statements. The multi table insert statement is regarded as a single statement, so only one sequence value will be generated and used for all data rows, which will lead to data problems.
  • Multiple table insert statements cannot be used with the execution plan stability function.
  • If the parallel hint is used for any target, the entire statement will be parallelized. If no target table uses the parallel hint, only the target table with the parallel attribute defined will be parallelized.
  • If any table in the multi table insert statement is an index organization table or defines a bitmap index, it will not be parallelized.

This is the end of this article about the detailed example of Oracle Data Warehouse ETL Technology multi table insert statement. For more information about Oracle multi table insert, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!