multi table insert 多表插入操作

本文连接 http://blog.itpub.net/29475508/viewspace-1684315/

普通的insert 操作，通常只能向一个表中插入数据。
使用multitable insert可以实现一条sql语句同时向多个表中插入数据。

数据源：
SCOTT@PROD>select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

创建测试表：emp10,emp20,emp30
SCOTT@PROD>create table emp10 as select empno,ename,deptno from emp where 1=2;
SCOTT@PROD>create table emp20 as select empno,ename,deptno from emp where 1=2;
SCOTT@PROD>create table emp30 as select empno,ename,deptno from emp where 1=2;

使用insert all 同时向三个表中插入数据
SCOTT@PROD>insert all
2 when deptno = 10 then
3 into emp10 values (empno,ename,deptno)
4 when deptno = 20 then
5 into emp20 values (empno,ename,deptno)
6 when deptno = 30 then
7 into emp30 values (empno,ename,deptno)
8 select empno,ename,deptno from emp;

查看三个表中数据
SCOTT@PROD>select * from emp10;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7782 CLARK 10
7839 KING 10
7934 MILLER 10

SCOTT@PROD>select * from emp20;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7566 JONES 20
7788 SCOTT 20
7876 ADAMS 20
7902 FORD 20

SCOTT@PROD>select * from emp30;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7499 ALLEN 30
7521 WARD 30
7654 MARTIN 30
7698 BLAKE 30
7844 TURNER 30
7900 JAMES 30

吕星昊
2015.6.3

ITPub博客

【SQL】 Multi table insert 多表插入操作

最新文章