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.