Overview of Multitable INSERT Statements

• The INSERT...SELECT statement can be used to insert rows into multiple tables as part of a single DML statement.
• Multitable INSERT statements can be used in data warehousing systems to transfer data from one or
more operational sources to a set of target tables.
• They provide significant performance improvement over:
– Single DML versus multiple INSERT...SELECT statements
– Single DML versus a procedure to do multiple inserts using IF...THEN syntax

Types of Multitable INSERT Statements


Oracle9i introduces the following types of multitable insert statements:
• Unconditional INSERT
• Conditional ALL INSERT
• Conditional FIRST INSERT
• Pivoting INSERT



Multitable INSERT Statements

Syntax

INSERT [ALL] [conditional_insert_clause]
[insert_into_clause values_clause] (subquery)

conditional_insert_clause

[ALL] [FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]


Unconditional INSERT ALL
• Select the EMPLOYEE_ID, HIRE_DATE, SALARY, and MANAGER_ID values from the EMPLOYEES table for those employees whose EMPLOYEE_ID is greater than 200.
• Insert these values into the SAL_HISTORY and MGR_HISTORY tables using a multitable INSERT.

INSERT ALL
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID, hire_date HIREDATE,
           salary SAL, manager_id MGR
           FROM employees
           WHERE employee_id > 200;

8 rows created.




Conditional INSERT ALL
• Select the EMPLOYEE_ID, HIRE_DATE, SALARY and MANAGER_ID values from the EMPLOYEES table for those employees whose EMPLOYEE_ID is greater than 200.
• If the SALARY is greater than $10,000, insert these values into the SAL_HISTORY table using a
conditional multitable INSERT statement.
• If the MANAGER_ID is greater than 200, insert these values into the MGR_HISTORY table using a
conditional multitable INSERT statement.


INSERT ALL
WHEN SAL > 10000 THEN
  INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR > 200 THEN
  INTO mgr_history VALUES(EMPID,MGR,SAL)
  SELECT employee_id EMPID,hire_date HIREDATE,
                 salary SAL, manager_id MGR
  FROM employees
  WHERE employee_id > 200;

4 rows created.


Conditional FIRST INSERT
• Select the DEPARTMENT_ID , SUM(SALARY) and MAX(HIRE_DATE) from the EMPLOYEES table.
• If the SUM(SALARY) is greater than $25,000 then insert these values into the SPECIAL_SAL, using a
conditional FIRST multitable INSERT.
• If the first WHEN clause evaluates to true, the subsequent WHEN clauses for this row should be
skipped.
• For the rows that do not satisfy the first WHEN condition, insert into the HIREDATE_HISTORY_00,
or HIREDATE_HISTORY_99, or HIREDATE_HISTORY tables, based on the value in the HIRE_DATE
column using a conditional multitable INSERT.


INSERT FIRST
WHEN SAL > 25000 THEN
      INTO special_sal VALUES(DEPTID, SAL)
WHEN HIREDATE like ('%') THEN
     INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like ('%99%') THEN
     INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
ELSE
      INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE
 FROM employees
 GROUP BY department_id;

8 rows created.


Pivoting INSERT
• Suppose you receive a set of sales records from a nonrelational database table,
SALES_SOURCE_DATA in the following format:

EMPLOYEE_ID, WEEK_ID, SALES_MON, SALES_TUE, SALES_WED, SALES_THUR,
SALES_FRI

• You would want to store these records in the SALES_INFO table in a more typical relational
format:

EMPLOYEE_ID, WEEK, SALES

• Using a pivoting INSERT, convert the set of sales records from the nonrelational database table to
relational format.



INSERT ALL
INTO sales_info VALUES (employee_id,week_id,sales_MON)
INTO sales_info VALUES (employee_id,week_id,sales_TUE)
INTO sales_info VALUES (employee_id,week_id,sales_WED)
INTO sales_info VALUES (employee_id,week_id,sales_THUR)
INTO sales_info VALUES (employee_id,week_id, sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE, sales_WED, sales_THUR,sales_FRI
FROM sales_source_data;

5 rows created.