使用insert多表插入数据

在oracle9i之前，使用insert语句插入数据时，只能向单个表插入数据。从oracle9i开始，使用insert语句可以向多个表插入数据。

下面是为实验建立的空表，这些表与emp表具有相同的表结构

SQL> create table dept10 as (select * from emp where 1=2);

表已创建。

SQL> create table dept20 as select * from emp where 1=3;

表已创建。

SQL> create table dept30 as select * from emp where 1=3;

表已创建。

SQL> create table other as select * from emp where 1=3;

 

SQL> select * from dept10;

未选定行

SQL> select * from dept20;

未选定行

SQL> select * from dept30;

未选定行

SQL> select * from other;

未选定行

--查询各表均为空

然后就可以用一条sql语句向多个表中插入数据了

SQL> insert
  2  when deptno=10 then into dept10
  3  when deptno=20 then into dept20
  4  when deptno=30 then into dept30
  5  else into other
  6  select * from emp;

已创建14行。

这样就为多个表插入了数据，可以select查看下各表

SQL> select * from dept10;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

SQL> select * from dept20;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800        900         20
      7566 JONES      MANAGER         7839 02-4月 -81           2975        900         20
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000        900         20
      7876 ADAMS      CLERK           7788 23-5月 -87           1100        900         20
      7902 FORD       ANALYST         7566 03-12月-81           3000        900         20

SQL> select * from dept30;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
      7900 JAMES      CLERK           7698 03-12月-81            950                    30

已选择6行。

 

向多表插入数据还有个insert first 和insert all（insert）的区别

使用first操作符执行多表插入时，如果数据已经满足了先前条件，并且被插入到某表，那么该行数据在后续插入中将不会被再次使用。

已写入 file afiedt.buf

  1  insert
  2  when deptno=10 then into dept10
  3  when deptno=20 then into dept20
  4  when deptno=30 then into dept30
  5  when job='CLERK' then into other
  6* select * from emp
SQL> /

已创建18行。

SQL> select * from other;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800        900         20
      7876 ADAMS      CLERK           7788 23-5月 -87           1100        900         20
      7900 JAMES      CLERK           7698 03-12月-81            950                    30
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

 

SQL> ed
已写入 file afiedt.buf

  1  insert first
  2  when deptno=10 then into dept10
  3  when deptno=20 then into dept20
  4  when deptno=30 then into dept30
  5  when job='CLERK' then into other
  6* select * from emp
SQL> /

已创建14行。

SQL> select * from other;

未选定行

 

通过other表的不同变化情况就可以清楚的知道insert first和insert all的区别了。