Multi Table Insert

Multi-table Insert, Inserting into mutiple target table when a given condition match or insert into all tables the same data.

---

Syntax

 {INSERT {ALL | FIRST}
[WHEN <condition-1> THEN]
INTO <table1> [(col1, col2….)]
[VALUES (val1, val2…..)]

[WHEN <condition-2> THEN]
INTO <table2> [(col1, col2….)]
[VALUES (val1, val3…..)]
.
.
 [WHEN <condition-n> THEN]
INTO <table2> [(col1, col2….)]
[VALUES (val1, val3…..)]

 [ELSE
INTO <table3> [(col1, col2….)]
[VALUES (val5, val6…..)]
]
{Sub-query-definition}
 }

---

 ALL: when all is specified then each when statement is evaluated and if condition of a when statement is true then 
      values are inserted into the specified table.

 FIRST: only first matching WHEN clause for which the condition is true is evaluated. Rest of WHEN clauses are skipped.

 WHEN CONDITION: <condition-1> <condition-2> etc uses Identifiers which are defined in Sub-query clause.

 (col1, col2….): are column names of a given table in the WHEN clause.
 (val1, val3…..): refer to identifier in the Sub-query clause.

---

Description

This syntax allows insertion of values to different table if conditions defined for each table in WHEN clause is true. It also supports insertion to a table defined in ELSE clause if none of the conditions defined at WHEN clause are true. One can use ELSE clause to insert into any arbitrary table.

---

Example

Consider following tables:

   EMP (ID integer, LNAME varchar, FNAME varchar)
   SALARY (EID integer, SALARY integer)
   EMP_TARGET (EID integer, NAME varchar)

Let’s create log table:

   CREATE TABLE logtable (ID integer, NAME varchar, SALARY integer, DESCRIPTION varchar)

Insert command:

    INSERT ALL
        WHEN column3 like ‘Smith’ THEN
    INTO EMP_TARGET (EID, NAME)
    [VALUES (column1, column2)]
    ELSE
    INTO logtable (ID, NAME, SALARY, DESCRIPTION)
    VALUES (column1, column2, column4, ERR_MSG)

SELECT t1.ID AS column1, (t1.FNAME|| t1.LNAME) AS column2, 
       t1.LNAME AS column3, t2.SALARY AS column4 
FROM EMP t1, SALARY t2 
     INNER JOIN ON (t1.ID = t2.EID) 
WHERE (t1.ID > 1000 AND t2.SALARY > 5000)

---

AxionDatabase and Oracle9i both supports this command. See also : UpsertCommand