Insert when, Insert all when difference
[B]Insert when syntax is [/B]
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;
[B]Insert all when syntax is[/B]
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 <table_name>;
What is the difference in their functionality?
Re: Insert when, Insert all when difference
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:<BR>
<BR>
-- Unconditional insert into ALL tables
INSERT ALL<BR>
INTO sal_history VALUES(empid,hiredate,sal)<BR>
INTO mgr_history VALUES(empid,mgr,sysdate) <BR>
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200; <BR>
-- Pivoting insert to split non-relational data
INSERT ALL<BR>
INTO Sales_info VALUES (employee_id,week_id,sales_MON)<BR>
INTO Sales_info VALUES (employee_id,week_id,sales_TUE)<BR>
INTO Sales_info VALUES (employee_id,week_id,sales_WED)<BR>
INTO Sales_info VALUES (employee_id,week_id,sales_THUR)<BR>
INTO Sales_info VALUES (employee_id,week_id, sales_FRI)<BR>
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
sales_WED, sales_THUR,sales_FRI
FROM Sales_source_data; <BR>
-- Conditionally insert into ALL tables
INSERT ALL <BR>
WHEN SAL>10000 THEN <BR>
INTO sal_history VALUES(EMPID,HIREDATE,SAL) <BR>
WHEN MGR>200 THEN <BR>
INTO mgr_history VALUES(EMPID,MGR,SYSDATE) <BR>
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200; <BR>
-- Insert into the FIRST table with a matching condition <BR>
INSERT FIRST <BR>
WHEN SAL > 25000 THEN <BR>
INTO special_sal VALUES(DEPTID,SAL) <BR>
WHEN HIREDATE like ('%00%') THEN <BR>
INTO hiredate_history_00 VALUES(DEPTID,HIREDATE) <BR>
WHEN HIREDATE like ('%99%') THEN <BR>
INTO hiredate_history_99 VALUES(DEPTID,HIREDATE) <BR>
ELSE <BR>
INTO hiredate_history VALUES(DEPTID, HIREDATE) <BR>
SELECT department_id DEPTID, SUM(salary) SAL, <BR>
MAX(hire_date) HIREDATE <BR>
FROM employees GROUP BY department_id; <BR>
The restrictions on multitable INSERTs are: <BR>
Multitable inserts can only be performed on tables, not on views or materialized views. <BR>
You cannot perform a multitable insert via a DB link. <BR>
You cannot perform multitable inserts into nested tables. <BR>
The sum of all the INTO columns cannot exceed 999. <BR>
Sequences cannot be used in the subquery of the multitable insert statement. <BR>