Oracle Tips - Multiple Tables INSERT

Scenario

Considering a scenario, you have billions of records in a table in a data warehouse which accumulates data from transactional tables for years. Now you will need to generate reports by using a part of the data in that table, which could be data associated with a given business process or within a given period of time. Real-time query will be time consuming, and take up computing resources as well. It may be a practical solution to periodically populate the required data to tables sharing similar table structure in data marts. Then access those tables for reporting. When it comes to SQL, Oracle provides a convenient clause to get this done, as illustrated below. 

Syntax:

INSERT [ALL | FIRST]

WHEN <Condition 1> THEN

    INTO <Target Table 1> VALUES (Columns)

    [INTO <Target Table 2> VALUES (Columns)] ...

[WHEN <Condition 2> THEN

    INTO <Target Table 3> VALUES (Columns)

    [INTO <Target Table 4> VALUES (Columns)] ...

]

[...]

[ELSE

    INTO <Target Table n> VALUES (Columns)

    [INTO <Target Table n+1> VALUES (Columns)] ...

]

SELECT Columns FROM Source Table

;

Data is fetched from the source table through a sub-query clause, and inserted into multiple target tables by using conditions to guide what data to which table.

Option FIRST, a record can only be added into one table when the specified condition is met at first time.

Option ALL, a record can be appended to multiple tables as long as those corresponding conditions are true.

Let's work through an example as follows.

Example

 POS_ALLPROD table keeps all products data. Those data needs to be split and loaded to sub tables. Each of the sub tables is dedicated to one product. Their structures are listed as below. As you can see, sub tables have one less column named by PRODUCT. 

Here are the data records in POS_ALLPROD.

First, we use Option FIRST to build the statement. Please pay attention to the conditions marked with  red color. STOCK data meets the condition for table POS_STOCK as well as the condition for table POS_OPTION. This is intentionally designed in order to demonstrate the difference between Option FIRST and Option ALL. 

INSERT FIRST

WHEN TRIM(PRODUCT) = 'STOCK' THEN

  INTO POS_STOCK VALUES (CUSTOMER_ID, POSITION, REGION, VALUEDATE)

WHEN TRIM(PRODUCT) = 'FUND' THEN

  INTO POS_FUND VALUES (CUSTOMER_ID, POSITION, REGION, VALUEDATE)

WHEN TRIM(PRODUCT) = 'BOND' THEN

  INTO POS_BOND VALUES (CUSTOMER_ID, POSITION, REGION, VALUEDATE)

WHEN TRIM(PRODUCT) = 'OPTION' OR TRIM(PRODUCT) = 'STOCK' THEN

  INTO POS_OPTION VALUES (CUSTOMER_ID, POSITION, REGION, VALUEDATE)

SELECT PRODUCT, CUSTOMER_ID, POSITION, REGION, VALUEDATE

FROM POS_ALLPROD

;

Obviously, the STOCK records didn't come into table POS_OPTION.

After truncated all the sub tables, now we use Option ALL instead of Option FIRST in the SQL script.

INSERT ALL

WHEN TRIM(PRODUCT) = 'STOCK' THEN

  INTO POS_STOCK VALUES (CUSTOMER_ID, POSITION, REGION, VALUEDATE)

WHEN TRIM(PRODUCT) = 'FUND' THEN

  INTO POS_FUND VALUES (CUSTOMER_ID, POSITION, REGION, VALUEDATE)

WHEN TRIM(PRODUCT) = 'BOND' THEN

  INTO POS_BOND VALUES (CUSTOMER_ID, POSITION, REGION, VALUEDATE)

WHEN TRIM(PRODUCT) = 'OPTION' OR TRIM(PRODUCT) = 'STOCK' THEN

  INTO POS_OPTION VALUES (CUSTOMER_ID, POSITION, REGION, VALUEDATE)

SELECT PRODUCT, CUSTOMER_ID, POSITION, REGION, VALUEDATE

FROM POS_ALLPROD

; 

The STOCK records are findable in both POS_STOCK and POS_OPTION.

Needless to say, we can use multiple SQLs to realize the same function. If we look at more options in the market, other tools like ETL can do the same thing as well. However, we could conclude that multiple tables insertion provides a simplified view for the above operation.