INSERT ALL
Since Oracle 9 we can perform multi-table inserts, allowing us to distribute the data from one source in one or more tables.
For the first example we have a sales table (can be external or remote) like this:
SELECT * FROM sales;
DEALER Q1 Q2 Q3 Q4
——- —- —- —- —-
SMALL CITY 52 54 34 61
CITY1 105 223 152 287
CITY2 138 234 209 256
CITY3 210 250 201 303
BIG CITY 1022 1394 1232 2161
Now we would like to insert each quarter on a different table
CREATE TABLE SALES_Q1 (Dealer varchar2(100),total number);
CREATE TABLE SALES_Q2 (Dealer varchar2(100),total number);
CREATE TABLE SALES_Q3 (Dealer varchar2(100),total number);
CREATE TABLE SALES_Q4 (Dealer varchar2(100),total number);
before Multi-table Inserts we would have to perform 4 different inserts:
insert INTO SALES_Q1 SELECT dealer,q1 FROM sales;
insert INTO SALES_Q2 SELECT dealer,q2 FROM sales;
insert INTO SALES_Q3 SELECT dealer,q3 FROM sales;
insert INTO SALES_Q4 SELECT dealer,q4 FROM sales;
Now we can use only one instruction to do the same:
INSERT ALL
INTO SALES_Q1 VALUES (dealer,q1 )
INTO SALES_Q2 VALUES (dealer,q2 )
INTO SALES_Q3 VALUES (dealer,q3 )
INTO SALES_Q4 VALUES (dealer,q4 )
SELECT * FROM sales;
Benefits? Less code and only one full table scan! (Very useful for large loads and ETL)
As you can see the command is an INSERT ALL, this means that is not conditioned and all rows are going to be inserted.
For the second example, we are going to add a condition, first, we create 3 tables
CREATE TABLE YEAR_LOW_SALES (Dealer varchar2(100),total number);
CREATE TABLE YEAR_MID_SALES (Dealer varchar2(100),total number);
CREATE TABLE YEAR_HIGH_SALES (Dealer varchar2(100),total number);
Now we are going to add the quarters and distribute the records according to the total
INSERT ALL
WHEN (q1+q2+q3+q4 <= 500) THEN
INTO YEAR_LOW_SALES VALUES (dealer,q1+q2+q3+q4 )
WHEN (q1+q2+q3+q4 > 500 and q1+q2+q3+q4 <= 1000) THEN
INTO YEAR_MID_SALES VALUES (dealer,q1+q2+q3+q4 )
WHEN (q1+q2+q3+q4 > 1000) THEN
INTO YEAR_HIGH_SALES VALUES (dealer,q1+q2+q3+q4 )
SELECT * FROM sales;
SELECT * FROM YEAR_LOW_SALES;
DEALER TOTAL
——- ——-
SMALL CITY 201
SELECT * FROM YEAR_MID_SALES;
DEALER TOTAL
——- ——-
CITY 1 767
CITY 2 837
CITY 3 964
SELECT * FROM YEAR_HIGH_SALES;
DEALER TOTAL
——- ——-
BIG CITY 5809
We can also use the ELSE clause like this:
INSERT ALL
WHEN (q1+q2+q3+q4 <= 500) THEN
INTO YEAR_LOW_SALES VALUES (dealer,q1+q2+q3+q4 )
WHEN (q1+q2+q3+q4 > 500 and q1+q2+q3+q4 <= 1000) THEN
INTO YEAR_MID_SALES VALUES (dealer,q1+q2+q3+q4 )
ELSE
INTO YEAR_HIGH_SALES VALUES (dealer,q1+q2+q3+q4 )
SELECT * FROM sales;
INSERT FIRST
We can also use the INSERT FIRST clause if we do, then the FIRST condition specified in the statement that is true will be applied by the RDBMS and it will stop evaluating the rest of the conditions.
In the next statement, I modified the second WHEN
before: WHEN (q1+q2+q3+q4 > 500 and q1+q2+q3+q4 <= 1000) THEN
after: WHEN (q1+q2+q3+q4 <= 1000) THEN
AND change the ALL clause by FIRST
INSERT FIRST
WHEN (q1+q2+q3+q4 <= 500) THEN
INTO YEAR_LOW_SALES VALUES (dealer,q1+q2+q3+q4 )
WHEN (q1+q2+q3+q4 <= 1000) THEN
INTO YEAR_MID_SALES VALUES (dealer,q1+q2+q3+q4 )
ELSE
INTO YEAR_HIGH_SALES VALUES (dealer,q1+q2+q3+q4 )
SELECT * FROM sales;
Because of the FIRST clause, When Oracle evaluates the second condition the first was already false, therefore this insert will give us the same results as the last two.