1.无条件的insert all into
将EMP表的ename,hiredate,mgr,sal列的值分别插入到sal_history和mgr_history中
SELECT ENAME,HIREDATE,MGR,SAL
FROM EMP;
ENAME HIREDATE MGR SAL
SMITH 1980/12/17 7902 800.00
ALLEN 1981/2/20 7698 1600.00
WARD 1981/2/22 7698 1250.00
JONES 1981/4/2 7839 2975.00
MARTIN 1981/9/28 7698 1250.00
BLAKE 1981/5/1 7839 2850.00
CLARK 1981/6/9 7839 2450.00
SCOTT 1987/4/19 7566 3000.00
KING 1981/11/17 5000.00
TURNER 1981/9/8 7698 1500.00
ADAMS 1987/5/23 7788 1100.00
JAMES 1981/12/3 7698 950.00
FORD 1981/12/3 7566 3000.00
MILLER 1982/1/23 7782 1300.00
CREATE TABLE SAL_HISTORY AS SELECT ENAME,HIREDATE,SAL FROM EMP WHERE 1 = 0 ;
CREATE TABLE MGR_HISTORY AS SELECT ENAME,MGR,SAL FROM EMP WHERE 1 = 0;
INSERT ALL INTO SAL_HISTORY VALUES(ENAME,HIREDATE,SAL)
INTO MGR_HISTORY VALUES (ENAME,MGR,SAL)
SELECT ENAME,HIREDATE,MGR,SAL
FROM EMP;
INSERT ALL INTO后
SELECT * FROM SAL_HISTORY;
ENAME HIREDATE SAL
SMITH 1980/12/17 800.00
ALLEN 1981/2/20 1600.00
WARD 1981/2/22 1250.00
JONES 1981/4/2 2975.00
MARTIN 1981/9/28 1250.00
BLAKE 1981/5/1 2850.00
CLARK 1981/6/9 2450.00
SCOTT 1987/4/19 3000.00
KING 1981/11/17 5000.00
TURNER 1981/9/8 1500.00
ADAMS 1987/5/23 1100.00
JAMES 1981/12/3 950.00
FORD 1981/12/3 3000.00
MILLER 1982/1/23 1300.00
SELECT * FROM MGR_HISTORY;
ENAME MGR SAL
SMITH 7902 800.00
ALLEN 7698 1600.00
WARD 7698 1250.00
JONES 7839 2975.00
MARTIN 7698 1250.00
BLAKE 7839 2850.00
CLARK 7839 2450.00
SCOTT 7566 3000.00
KING 5000.00
TURNER 7698 1500.00
ADAMS 7788 1100.00
JAMES 7698 950.00
FORD 7566 3000.00
MILLER 7782 1300.00
2.带条件的insert all插入
TRUNCATE TABLE SAL_HISTORY;
TRUNCATE TABLE MGR_HISTORY;
INSERT ALL
WHEN SAL > 1000 THEN
INTO SAL_HISTORY VALUES (ENAME,HIREDATE,SAL)
WHEN MGR > 7000 THEN
INTO MGR_HISTORY VALUES (ENAME,MGR,SAL)
SELECT ENAME,HIREDATE,MGR,SAL FROM EMP;
SELECT * FROM SAL_HISTORY;
ENAME HIREDATE SAL
ALLEN 1981/2/20 1600.00
WARD 1981/2/22 1250.00
JONES 1981/4/2 2975.00
MARTIN 1981/9/28 1250.00
BLAKE 1981/5/1 2850.00
CLARK 1981/6/9 2450.00
SCOTT 1987/4/19 3000.00
KING 1981/11/17 5000.00
TURNER 1981/9/8 1500.00
ADAMS 1987/5/23 1100.00
FORD 1981/12/3 3000.00
MILLER 1982/1/23 1300.00
SELECT * FROM MGR_HISTORY;
ENAME MGR SAL
SMITH 7902 800.00
ALLEN 7698 1600.00
WARD 7698 1250.00
JONES 7839 2975.00
MARTIN 7698 1250.00
BLAKE 7839 2850.00
CLARK 7839 2450.00
SCOTT 7566 3000.00
TURNER 7698 1500.00
ADAMS 7788 1100.00
JAMES 7698 950.00
FORD 7566 3000.00
MILLER 7782 1300.00
3.INSERT FIRST的使用
TRUNCATE TABLE SAL_HISTORY;
TRUNCATE TABLE MGR_HISTORY;
INSERT FIRST
WHEN SAL > 3000 THEN --工资大于3000的员工
INTO MGR_HISTORY VALUES (ENAME,MGR,SAL)
WHEN SAL > 1000 THEN --工资大于1000但是小于或者等于3000的员工
INTO SAL_HISTORY VALUES (ENAME,HIREDATE,SAL)
SELECT ENAME,HIREDATE,MGR,SAL FROM EMP;
SELECT * FROM SAL_HISTORY ORDER BY SAL ASC ;
ENAME HIREDATE SAL
ADAMS 1987/5/23 1100.00
WARD 1981/2/22 1250.00
MARTIN 1981/9/28 1250.00
MILLER 1982/1/23 1300.00
TURNER 1981/9/8 1500.00
ALLEN 1981/2/20 1600.00
CLARK 1981/6/9 2450.00
BLAKE 1981/5/1 2850.00
JONES 1981/4/2 2975.00
FORD 1981/12/3 3000.00
SCOTT 1987/4/19 3000.00
SELECT * FROM MGR_HISTORY ORDER BY SAL ASC ;
ENAME MGR SAL
KING 5000.00
