1 over of multitable insert statements
1)the insert...select statement can be userd to insert row into multiple table as part of a single dml statement.
2) multitable insert statements can be used in data warehousing systems to transfer data from one or more operational sources to source to a set of target table.
3) they providde significant performance improvement over
single dml versuls multiple insert...select statement
single dml versus a proceedduree to do mutiple inserts using if ,,, then syntax.
2
unconditional insert
insert all
into sal_history values (EMPID,HIREDATE,SAL)
into mgr_history values (EMPID,MGR,SAL)
select employee_id EMPID,hire_date JIREDATE,
salary SAL,manager_id MGR
from employees
where employee_id>200;
3 Conditional insert all
insert all
when sal>1000 then
into sal_history values(empid,hiredate,sal)
when mgr>200 then
into mgr_history values(empid,mgr,sal)
select emp_id empid,hire_date hiredate,salary sal,manager_id mgr,
from employees
where employee_id>200;
4 Conditional first insert
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
insert hiredate_history_99 values(ddeptid,hiredate)
else
into hiredate_history values(deptid,hiredate)
select ddepartmeent_id deptid,sum(salary) sal,
max(hire_date) hiredate
from employees
group by department_id;
5 Pivoting insert
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,weekid,sales_mon,sales_tue,sales_wed,sales_thur,sales_fri
from sales_source_data;
6 create index with create table statement
create table new_emp
(employee_id number(6)
primary key using index
(create index emp_id_idx on new_emp(employee_id)),
first_name varchar2(20),
last_name varchar2(25)
)