Inserting into Multiple Tables

Most INSERT statements are the single-table variety, but Oracle also supports a multiple-table INSERT statement. You most frequently use multitable inserts in data warehouse Extract, Transform, and Load (ETL) routines.

With a multitable insert, you can make a single pass through the source data and load the data into more than one table. By reducing the number of passes through the source data, you can reduce the overall work and thus achieve faster throughput.

If a WHEN condition evaluates to TRUE, the corresponding INTO clause is executed. If no WHEN condition evaluates to TRUE, the ELSE clause is executed. The keyword ALL tells the database to check each WHEN condition. On the other hand, the keyword FIRST tells the database to stop checking
WHEN conditions after finding the first TRUE condition.

In the following example, an insurance company has policies for both property and casualty in the policy table, but in their data mart, they break out these policy types into separate fact tables. During the monthly load, new policies are added to both the property_premium_fact and casualty_premium_fact tables. You can use a multitable INSERT to add these rows more efficiently than two separate INSERT statements. The multitable INSERT would look like this:

INSERT FIRST WHEN policy_type = 'P' THEN INTO property_premium_fact(policy_id ,policy_nbr ,premium_amt)
VALUES (property_premium_seq.nextval ,policy_number ,gross_premium)
WHEN p.policy_type = 'C' THEN INTO property_premium_fact(policy_id
,policy_nbr ,premium_amt) VALUES (property_premium_seq.nextval
,policy_number ,gross_premium)
SELECT policy_nbr ,gross_premium ,policy_type
FROM policies WHERE policy_date >=
TRUNC(SYSDATE,'MM') - TO_YMINTERVAL('00-01');

By using this multitable INSERT statement instead of two separate statements, the code makes a single pass through the policy table instead of two and thus loads the data more efficiently.