Insert when, Insert all when difference

[krishnaindia2007]

Insert when syntax is
INSERT
WHEN condition THEN
INTO table_name column_list
VALUES (values_list)
WHEN condition THEN
INTO table_name (column_list)
VALUES (values_list)
ELSE
INTO table_name (column_list)
VALUES (values_list)
SELECT column_list FROM table_name;

Insert all when syntax is
INSERT ALL
WHEN (condition) THEN
INTO table_name (column_list)
VALUES (values_list)
WHEN (condition) THEN
INTO table_name (column_list)
VALUES (values_list)
ELSE
INTO table_name (column_list)
VALUES (values_list)
SELECT column_list FROM ;

What is the difference in their functionality?

[susarlasireesha]

Insert All functionality
Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions. It's main use is for the ETL process in data warehouses where it can be parallelized and/or convert non-relational data into a relational format:



-- Unconditional insert into ALL tables
INSERT ALL

INTO sal_history VALUES(empid,hiredate,sal)

INTO mgr_history VALUES(empid,mgr,sysdate)

SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200;


-- Pivoting insert to split non-relational data
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;


-- Conditionally insert into ALL tables
INSERT ALL

WHEN SAL>10000 THEN

INTO sal_history VALUES(EMPID,HIREDATE,SAL)

WHEN MGR>200 THEN

INTO mgr_history VALUES(EMPID,MGR,SYSDATE)

SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200;


-- Insert into the FIRST table with a matching condition

INSERT FIRST

WHEN SAL > 25000 THEN

INTO special_sal VALUES(DEPTID,SAL)

WHEN HIREDATE like ('%00%') 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;

The restrictions on multitable INSERTs are:


Multitable inserts can only be performed on tables, not on views or materialized views.

You cannot perform a multitable insert via a DB link.

You cannot perform multitable inserts into nested tables.

The sum of all the INTO columns cannot exceed 999.

Sequences cannot be used in the subquery of the multitable insert statement.