Multi-Table Inserts

Oracle9i New Feature Series: Multi-table Inserts

Inserts to multiple tables in a single INSERT statement are a significant addition to the Oracle9i database ETL features. This allows the INSERT INTO SELECT statement to load data to multiple target tables. A single scan can perform inserts to multiple target tables [based on the conditions you specify - optional].

Prior to Oracle9i, you may have to write multiple INSERT statements for each table or use a PL/SQL cursor to insert into different tables.

The syntax of multi-table insert is:

INSERT { ALL insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
| conditional_insert_clause
}
subquery

where the conditional_insert_clause is

[ ALL | FIRST ]
WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
[WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
]...
[ELSE insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
]

The INSERT ALL clause inserts rows into the target tables unconditionally. Each row read is processed against each INSERT clause.

The following example takes each row from SALES_HISTORY table and inserts into the SALES_MONTHLY table a total amount for each month (flat table to a normalized table):

DESC SALES_HISTORY
Name Null? Type
----------------------------------------- -------- -------------
YEAR NUMBER(4)
REGION CHAR (2)
JAN NUMBER
FEB NUMBER
MAR NUMBER
APR NUMBER
MAY NUMBER
JUN NUMBER
JUL NUMBER
AUG NUMBER
SEP NUMBER
 OCT NUMBER
NOV NUMBER
DEC NUMBER
 
DESC SALES_MONTHLY
Name Null? Type
----------------------------------------- -------- -------------
MONTH_YEAR DATE
AMOUNT NUMBER
 
INSERT ALL
INTO SALES_MONTHLY (MONTH_YEAR, AMOUNT)
VALUES (TO_DATE('01/'||YEAR,'MM/YYYY'), JAN)
INTO SALES_MONTHLY VALUES (TO_DATE('02/'||YEAR,'MM/YYYY'), FEB)
INTO SALES_MONTHLY VALUES (TO_DATE('03/'||YEAR,'MM/YYYY'), MAR)
INTO SALES_MONTHLY VALUES (TO_DATE('04/'||YEAR,'MM/YYYY'), APR)
INTO SALES_MONTHLY VALUES (TO_DATE('05/'||YEAR,'MM/YYYY'), MAY)
INTO SALES_MONTHLY VALUES (TO_DATE('06/'||YEAR,'MM/YYYY'), JUN)
INTO SALES_MONTHLY VALUES (TO_DATE('07/'||YEAR,'MM/YYYY'), JUL)
INTO SALES_MONTHLY VALUES (TO_DATE('08/'||YEAR,'MM/YYYY'), AUG)
INTO SALES_MONTHLY VALUES (TO_DATE('09/'||YEAR,'MM/YYYY'), SEP)
INTO SALES_MONTHLY VALUES (TO_DATE('10/'||YEAR,'MM/YYYY'), OCT)
INTO SALES_MONTHLY VALUES (TO_DATE('11/'||YEAR,'MM/YYYY'), NOV)
INTO SALES_MONTHLY VALUES (TO_DATE('12/'||YEAR,'MM/YYYY'), DEC)
SELECT YEAR,
SUM(JAN) JAN, SUM(FEB) FEB, SUM(MAR) MAR, SUM(APR) APR,
SUM(MAY) MAY, SUM(JUN) JUN, SUM(JUL) JUL, SUM(AUG) AUG,
SUM(SEP) SEP, SUM(OCT) OCT, SUM(NOV) NOV, SUM(DEC) DEC
FROM SALES_HISTORY
GROUP BY YEAR
/

The ALL in the conditional insert clause makes Oracle evaluate all the WHEN conditions irrespective of the other WHEN conditions. The FIRST clause stops evaluating other WHEN conditions the first time any condition is evaluated true. The conditions are evaluated in the order they appear.

INSERT FIRST
WHEN REGION = 'TX' THEN
INTO SALES_TEXAS (YEAR, TOTAL_AMOUNT)
VALUES (YEAR, TOTAMT)
WHEN REGION = 'CA' THEN
INTO SALES_CALIF (YEAR, TOTAL_AMOUNT)
VALUES (YEAR, TOTAMT)
WHEN REGION = 'NM' THEN
INTO SALES_NEWMEX (YEAR, TOTAL_AMOUNT)
VALUES (YEAR, TOTAMT)
WHEN REGION = 'AZ' THEN
INTO SALES_ARIZ (YEAR, TOTAL_AMOUNT)
VALUES (YEAR, TOTAMT)
ELSE
INTO SALES_OTHER (YEAR, TOTAL_AMOUNT)
VALUES (YEAR, TOTAMT)
SELECT YEAR, REGION,
(JAN+FEB+MAR+APR+MAY+JUN+JUL+AUG+SEP+OCT+NOV+DEC) TOTAMT
FROM SALES_HISTORY
/

If the ELSE clause is omitted, Oracle takes no action on that row.

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.