表的插入、更新、删除、合并操作_4_同时往多个表插入记录

                          同时往多个表插入记录

需求描述

需求：从dept表里插入数据到3张表,当loc是NEW YORK和BOSTON时向dept_east表中插入,当当loc是CHICAGO时向dept_mid表中插入,其它情况往dept_west表中插入.

解决方法:这里通过INSERT ALL WHEN Condition THEN INTO TABLENAME VALUES (…)的方式.

 

SQL代码

-- Oracle:

CREATE TABLE dept_east
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );


CREATE TABLE dept_mid
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );


CREATE TABLE dept_west
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );


INSERT ALL
WHEN loc IN ('NEW YORK','BOSTON') THEN
INTO dept_east(deptno,dname,loc) VALUES(deptno,dname,loc)
WHEN loc IN ('CHICAGO') THEN
INTO dept_mid(deptno,dname,loc) VALUES(deptno,dname,loc)
ELSE
INTO dept_west(deptno,dname,loc) VALUES(deptno,dname,loc)
SELECT  deptno,dname,loc FROM dept


-- 我们可以通过如下的SQL,清晰的看到分散到3个表的数据:
SELECT A.*,'dept_east' AS TableSource FROM dept_east A
UNION ALL
SELECT A.*,'dept_mid' AS TableSource FROM dept_mid A
UNION ALL
SELECT A.*,'dept_west' AS TableSource FROM dept_west A;

执行结果 

注: 截止目前,仅oracle支持该语法.