一次向多个表中插入记录

问题描述
有时需要将一个表中的数据按照一定的条件分别一次性插入到多个表中，怎样实现？

ORACLE
使用INSERT ALL 或INSERT FIRST语句。这两种方法除了关键字ALL和FIRST不同外，其语法相同。下面例子使用INSERT ALL

CREATE TABLE MEDIUM_ORDERS  AS SELECT * FROM SMALL_ORDERS 
CREATE TABLE LARGER_ORDERS  AS SELECT * FROM SMALL_ORDERS 
CREATE TABLE SPECIAL_ORDERS AS SELECT * FROM SMALL_ORDERS 

INSERT ALL
 WHEN ORDER_TOTAL <1000 THEN
  INTO SMALL_ORDERS
 WHEN ORDER_TOTAL >1000 AND ORDER_TOTAL<2000 THEN
  INTO MEDIUM_ORDERS
 WHEN ORDER_TOTAL >2900 THEN
  INTO LARGER_ORDERS
 ELSE 
  INTO SPECIAL_ORDERS
SELECT * FROM OE.ORDERS

DB2
将所有目标用UNION ALL构成一个内联视图，并以该内联视图作为INSERT INTO的目标。必须要在这些表中设置约束条件，以确保这些行插入到正确的表中。

create table dept_east
(deptno interger,
dname varchar(10),
loc varchar(10) check (loc in ('NEW YORK','BOSTON'))
)

create table dept_mid
(deptno interger,
dname varchar(10),
loc varchar(10) check (loc ='CHICAGO')
)

create table dept_west
(deptno interger,
dname varchar(10),
loc varchar(10) check (loc ='DALLAS')

insert into (select * from dept_east union all
             select * from dept_mid  union all
             select * from dept_west union all
) select * from dept