insert all的高级用法

insert all

向多个表插入数据

SCOTT@ db>drop table e1;

Table dropped.

Elapsed: 00:00:00.32
SCOTT@ db>drop table e2;

Table dropped.

Elapsed: 00:00:00.04e
SCOTT@ db>
SCOTT@ db>create table e1 as select ename,sal,hiredate from emp where 1=0;

Table created.

Elapsed: 00:00:00.09
SCOTT@ db>create table e1 as select ename,deptno,mgr from emp where 1=0;
create table e1 as select ename,deptno,mgr from emp where 1=0
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

Elapsed: 00:00:00.01
SCOTT@ db>create table e2 as select ename,deptno,mgr from emp where 1=0;

Table created.

Elapsed: 00:00:00.03
SCOTT@ db>insert all
  2  into e1 values (ename,sal,hiredate)
  3  into e2 values (ename,deptno,mgr)
  4  select  ename,sal,hiredate,deptno,mgr from emp
  5  where deptno=10;

6 rows created.

Elapsed: 00:00:00.03
SCOTT@ db>select * from e1;

ENAME          SAL HIREDATE
---------- ---------- ---------
CLARK         2450 09-JUN-81
KING         5000 17-NOV-81
MILLER         1300 23-JAN-82

Elapsed: 00:00:00.03
SCOTT@ db>select * from e2;

ENAME           DEPTNO         MGR
---------- ---------- ----------
CLARK           10        7839
KING           10
MILLER           10        7782

Elapsed: 00:00:00.00