一次向多个表中插入记录
问题描述有时需要将一个表中的数据按照一定的条件分别一次性插入到多个表中，怎样实现？
ORACLE
使用INSERT ALL 或INSERT FIRST语句。这两种方法除了关键字ALL和FIRST不同外，其语法相同。下面例子使用INSERT ALL
CREATE TABLE MEDIUM_ORDERSAS SELECT * FROM SMALL_ORDERS CREATE TABLE LARGER_ORDERSAS SELECT * FROM SMALL_ORDERS CREATE TABLE SPECIAL_ORDERS AS SELECT * FROM SMALL_ORDERS INSERT ALL WHEN ORDER_TOTAL <1000 THENINTO SMALL_ORDERS WHEN ORDER_TOTAL >1000 AND ORDER_TOTAL<2000 THENINTO MEDIUM_ORDERS WHEN ORDER_TOTAL >2900 THENINTO LARGER_ORDERS ELSE INTO SPECIAL_ORDERSSELECT * 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_midunion all select * from dept_west union all) select * from dept
页:
[1]