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)
? )