Multi table Inserts

Since Oracle 9 we can perform multitable 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;


No 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;

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.

Check the INSERT Oracle documentation here

Advertisements
This entry was posted in Oracle Server, Schema, SQL and tagged , , , , , . Bookmark the permalink.

One Response to Multi table Inserts

  1. moin88 says:

    nice, got the multitable insert concept….

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s