一条指令分条件向多个表插入数据的方法
1 以EMP表作为实验,要求:
部门10的记录插入 E1表,
部门20的记录插入 E2表,
部门30的记录插入 E3表,
部门40的记录插入 E4表,
其中E1、E2、E3、E4表结构与EMP表部分或者全部相同
语句:
--表结构相同时
INSERT ALL
WHEN DEPTNO=10 THEN INTO E1
WHEN DEPTNO=20 THEN INTO E2
WHEN DEPTNO=30 THEN INTO E3
ELSE INTO E4
SELECT * FROM EMP;
1 以EMP表作为实验,要求:
部门10的记录插入 E1表,
部门20的记录插入 E2表,
部门30的记录插入 E3表,
部门40的记录插入 E4表,
其中E1、E2、E3、E4表结构与EMP表部分或者全部相同
语句:
--表结构相同时
INSERT ALL
WHEN DEPTNO=10 THEN INTO E1
WHEN DEPTNO=20 THEN INTO E2
WHEN DEPTNO=30 THEN INTO E3
ELSE INTO E4
SELECT * FROM EMP;
--表结构不同时
CREATE TABLE E2 AS SELECT EMPNO FROM EMP WHERE 1=2;
CREATE TABLE E3 AS SELECT HIREDATE FROM EMP WHERE 1=2;
CREATE TABLE E4 AS SELECT SAL FROM EMP WHERE 1=2;
CREATE TABLE E5 AS SELECT COMM FROM EMP WHERE 1=2;
CREATE TABLE E6 AS SELECT MGR,JOB FROM EMP WHERE 1=2;
CREATE TABLE E2 AS SELECT EMPNO FROM EMP WHERE 1=2;
CREATE TABLE E3 AS SELECT HIREDATE FROM EMP WHERE 1=2;
CREATE TABLE E4 AS SELECT SAL FROM EMP WHERE 1=2;
CREATE TABLE E5 AS SELECT COMM FROM EMP WHERE 1=2;
CREATE TABLE E6 AS SELECT MGR,JOB FROM EMP WHERE 1=2;
INSERT ALL
WHEN DEPTNO=10 THEN INTO E2(EMPNO)VALUES(EMPNO)
WHEN DEPTNO=20 THEN INTO E3(HIREDATE)VALUES(HIREDATE)
WHEN DEPTNO=30 THEN INTO E4(SAL)VALUES(SAL)
WHEN DEPTNO=40 THEN INTO E5(COMM)VALUES(COMM)
ELSE INTO E6(MGR,JOB)VALUES(MGR,JOB)
SELECT * FROM EMP;
WHEN DEPTNO=10 THEN INTO E2(EMPNO)VALUES(EMPNO)
WHEN DEPTNO=20 THEN INTO E3(HIREDATE)VALUES(HIREDATE)
WHEN DEPTNO=30 THEN INTO E4(SAL)VALUES(SAL)
WHEN DEPTNO=40 THEN INTO E5(COMM)VALUES(COMM)
ELSE INTO E6(MGR,JOB)VALUES(MGR,JOB)
SELECT * FROM EMP;
注意:INSERT ALL 和INSERT FIRST的区别
--------------灵活运用