pivoting Insert :行列转换多表插入
unconditional insert:oracle将执行每个insert,不进行排除
conditional all insert:有条件的insert all
conditional first insert:符合前面第一个的记录不再应用到下面的条件判断
示例:(参考)
-- 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; |
参考:
INSERT http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm#i2125362
这几种Insert有什么区别?
http://www.itpub.net/thread-1281678-1-1.html
sql入门之23 pivoting insert等
http://wmlm.itpub.net/post/12871/279365
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9537053/viewspace-746468/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9537053/viewspace-746468/