ITPub博客

首页 > Linux操作系统 > Linux操作系统 > multi_table_insert

multi_table_insert

原创 Linux操作系统 作者:treesofthehill 时间:2012-10-15 15:36:59 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: SESSION_PRIVS
请登录后发表评论 登录
全部评论

注册时间:2008-06-11

  • 博文量
    97
  • 访问量
    179788