Systems Engineering and RDBMS

Multiple table inserts using a single select statement

Posted by decipherinfosys on January 20, 2009

Normally most of our INSERT statements insert data into a single table only.  We can chose to do insert into…values() or insert into…select statement.  In OLTP systems, some times we come across situations where we may have to enter data into multiple tables from the same select statement.  This is more common in data warehouse applications. Oracle supports multiple-table insert using single select statement. Whenever we are using values clause, we have to use single table insert statement but whenever we have to insert data from a single table to more than one table, multiple-table insert statement comes in very handy.

The advantage of using multiple-table insert is that we have to query table only once in order to insert data into tables rather than querying table for each individual table in which we want to insert data.

Multiple-insert table has two variations.
INSERT FIRST:  When FIRST keyword is used, database evaluates each WHEN clause in the order it appears. If any one of the WHEN clause evaluates to TRUE then database use corresponding INTO clause. All the other WHEN clauses after that are ignored.
INSERT ALL: when ALL keyword is used, database evaluates each WHEN clause irrespective of result of any WHEN clause. Even if one WHEN clause evaluates to TRUE, all the other WHEN clauses after that are evaluated.

ELSE clause can be used in INSERT statement as well. If ELSE clause is specified, and none of the WHEN clause evaluates to TRUE, corresponding INTO clause is executed for a specific record. If ELSE clause is not specified than specific row is ignored and no action is taken.

We will follow this with an example. We will create three tables to put data from single table to multiple tables. To keep it simple, we will create and populate our table from user_objects table. Connect to SQL*Plus using proper credentials.

CREATE TABLE test_object AS
SELECT object_id,object_name,object_type,status
FROM user_objects;

Table created.

CREATE TABLE test_object_tables AS
SELECT object_id,object_name,object_type,status
FROM user_objects
WHERE 1=0;

Table created.

CREATE TABLE test_object_others AS
SELECT object_id,object_name,object_type,status
FROM user_objects
WHERE 1=0;

Table created.

First table contains the data from user_objects table while other two tables are empty tables. Using multiple-table insert statement we are going to insert data into these two tables. It uses WHEN condition to evaluate the expression to insert data conditionally in tables. Now let us write multi-table insert to insert data into remaining two tables.

—- Using INSERT FIRST clause.
INSERT FIRST
WHEN object_Type = ‘TABLE’ THEN
INTO test_object_tables
VALUES(object_id,object_name,object_type,status)
WHEN object_Type IN (‘TABLE’,’PROCEDURE’) THEN
INTO test_object_others
VALUES(object_id,object_name,object_type,status)
SELECT object_id,object_name,object_type,status
FROM test_object
WHERE object_name LIKE ‘%TEST%’
/

COMMIT;

Here is the result.

SELECT object_name,object_Type
FROM test_object_tables;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------
TEST_TAB                       TABLE
TEST_TABLE                     TABLE
TEST                           TABLE
TEST_OBJECT                    TABLE

SELECT object_name,object_Type
FROM test_object_others;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------
TEST_PROC                      PROCEDURE
OUTTEST                        PROCEDURE

As mentioned earlier, in FIRST clause, once first WHEN clause evaluates to TRUE, rest of the WHEN clauses are ignored. Even though next WHEN clause indicates we want to insert table and procedure object types into test_object_others table, it only inserted procedure object types only.

Now let us use INSERT ALL clause and examine the results again.

—- Using INSERT ALL clause.
INSERT ALL
WHEN object_Type = ‘TABLE’ THEN
INTO test_object_tables
VALUES(object_id,object_name,object_type,status)
WHEN object_Type IN (‘TABLE’,’PROCEDURE’) THEN
INTO test_object_others
VALUES(object_id,object_name,object_type,status)
SELECT object_id,object_name,object_type,status
FROM test_object
WHERE object_name LIKE ‘%TEST%’
/

Here is the result.

SELECT object_name,object_Type
FROM test_object_tables;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------
TEST_TAB                       TABLE
TEST_TABLE                     TABLE
TEST                           TABLE
TEST_OBJECT                    TABLE

SELECT object_name,object_Type
FROM test_object_others;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------
TEST_PROC                      PROCEDURE
OUTTEST                        PROCEDURE
TEST_TAB                       TABLE
TEST_TABLE                     TABLE
TEST                           TABLE
TEST_OBJECT                    TABLE

In ALL clause, database also evaluated second WHEN clause even though first WHEN clause evaluated to TRUE. So we have records for both table and procedure object types.

Major benefit of it will be observed during the data loading process as in just one pass it will get the data and will load it into multiple tables.  Less database resources are used which results into performance improvement.

3 Responses to “Multiple table inserts using a single select statement”

  1. Serghei Stasiv said

    We are have each version of our SPs in Oracle and in MSSQL. I am trying to code simmilar INSERT into multiple tables from one SELECT in MSSQL and can not find any analogs.
    Do you know any analog of this in MSSQL?

    Much appreciate your help!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: