New Features / Documentos / Soporte Técnico / Servicios / Tilsor
 
 

Oracle9i: Multi-table Insert Statements

Creation Date: 20-MAR-2001
Last Revision Date: 22-AUG-2001


PURPOSE
This article is intended to introduce the new multi-table insert statements
available in Oracle9i.


SCOPE & APPLICATION
This article assumes the reader has an understanding of the current SQL
INSERT .. SELECT statement.


RELATED DOCUMENTS
Oracle9i SQL Reference


Oracle9i Multi-table INSERT:
============================

The multi-table insert allows the INSERT .. SELECT statement to insert rows
into multiple tables in a single DML. This feature is very useful in a data
warehousing environment when loading data from operational sources into various
target tables. You can also use the multi-table insert feature to refresh
materialized views. Prior to Oracle9i, this had to be accomplished either with
multiple INSERT .. SELECT statements or with IF .. THEN logic.

Since the source data is only scanned once, the multi-table insert is much
faster. Just as with the traditional INSERT .. SELECT, the new multi-table
insert can be done in parallel and used with direct-load mechanisms to improve
performance. The decision to parallelize follows the same rules as current
parallel DML with the additional stipulation that ALL of the target tables must
be created with a parallel degree set (or use alter session force parallel DML).

The following types of multi-table inserts are available:

1. Unconditional INSERT
2. Pivoting INSERT
3. Conditional ALL INSERT
4. Conditional FIRST INSERT


1. Unconditional INSERT
-----------------------

The unconditional INSERT inserts the rows into all of the tables without any
conditions. All inserts statements will execute simultaneously.

Example:

INSERT ALL
INTO product_totals VALUES(product_id, today, total)
INTO product_history VALUES(product_id, today, quantity)
SELECT product_id, trunc(sysdate) today,
sum(unit_price*quantity) total, sum(quantity) quantity
FROM orders, order_items
WHERE orders.order_id = order_items.order_id
AND trunc(order_date) = trunc(sysdate)
GROUP BY product_id;


2. Pivoting INSERT
------------------

The pivoting INSERT inserts into the same table several times pivoting from a
non-normalized form to a normalized form.

Example:

INSERT ALL
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date, sales_sun)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)
SELECT product_id, customer_id, weekly_start_date, sales_sun,
sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
FROM sales_summary;


3. Conditional ALL INSERT
-------------------------

The conditional ALL INSERT evaluates each of the WHEN clauses to determine if
the row is inserted into each table. All WHEN clauses are evaluated, so some
source rows may be inserted into more than one target while others may not be
inserted at all. An optional ELSE clause can also be used.

Example:

INSERT ALL
WHEN order_mode='online'
THEN INTO web_orders
VALUES (order_id, order_date, order_total)
WHEN coupon is not null
THEN INTO coupon_sales
VALUES (order_id, order_date, order_total)
SELECT order_id, order_date, order_total, coupon, order_mode
FROM orders;

You can also use multiple INTO clauses with one WHEN condition:

INSERT ALL
WHEN order_mode='online'
THEN INTO web_orders
VALUES (order_id, order_date, order_total)
INTO web_history
VALUES (order_id, order_date, order_total)
SELECT order_id, order_date, order_total, coupon, order_mode
FROM orders;


4. Conditional FIRST INSERT
---------------------------

The conditional FIRST INSERT evaluates each WHEN clause in the order in which
it appears in the statement and only executes the INTO clause for the first one
which is TRUE. An optional ELSE clause can also be used.

Example:

INSERT FIRST
WHEN order_total > 10000 THEN
INTO priority_handling VALUES(order_id)
WHEN order_total > 5000 THEN
INTO special_handling VALUES(order_id)
WHEN order_total > 3000 THEN
INTO privilege_handling VALUES(order_id)
ELSE
INTO regular_handling VALUES(order_id)
SELECT order_id, order_total
FROM orders;