SQL 多表插入,你想把一个查询语句返回的结果集插入到多个目标表中。例如,你希望把 DEPT
表的数据分别插入到 DEPT_EAST
表、DEPT_WEST
表和 DEPT_MID
表。这 3 个表与 DEPT
表的结构相同(相同的列和数据类型),并且当前不含任何数据。
SQL 多表插入 问题描述
你想把一个查询语句返回的结果集插入到多个目标表中。例如,你希望把 DEPT
表的数据分别插入到 DEPT_EAST
表、DEPT_WEST
表和 DEPT_MID
表。这 3 个表与 DEPT
表的结构相同(相同的列和数据类型),并且当前不含任何数据。
SQL 多表插入 解决方案
解决办法就是把查询结果插入到多个目标表中。与 4.4 节的不同之处在于,这次的目标表不止一个。
Oracle
使用 INSERT ALL
或者 INSERT FIRST
语句。除 ALL
和 FIRST
关键字不同之外,二者的语法并无二致。下面使用 INSERT ALL
语句,它能够确保兼顾所有目标表。
1 insert all
2 when loc in ('NEW YORK','BOSTON') then
3 into dept_east (deptno,dname,loc) values (deptno,dname,loc)
4 when loc = 'CHICAGO' then
5 into dept_mid (deptno,dname,loc) values (deptno,dname,loc)
6 else
7 into dept_west (deptno,dname,loc) values (deptno,dname,loc)
8 select deptno,dname,loc
9 from dept
DB2
插入数据到一个内嵌视图,而该内嵌视图是对所有目标表执行 UNION ALL
得到的结果。你需要为各个目标表添加一个约束条件,确保每一行数据都会被插入到正确的表中。
create table dept_east
( deptno integer,
dname varchar(10),
loc varchar(10) check (loc in ('NEW YORK','BOSTON')))
create table dept_mid
( deptno integer,
dname varchar(10),
loc varchar(10) check (loc = 'CHICAGO'))
create table dept_west
( deptno integer,
dname varchar(10),
loc varchar(10) check (loc = 'DALLAS'))
1 insert into (
2 select * from dept_west union all
3 select * from dept_east union all
4 select * from dept_mid
5 ) select * from dept
MySQL、PostgreSQL 和 SQL Server
在写作本书时,这些数据库尚不支持多表插入。
SQL 多表插入 扩展知识
Oracle
Oracle 的多表插入使用 WHEN-THEN-ELSE
子句逐行评估嵌套 SELECT
语句所返回的结果,并将数据插入到相应的表中。虽然就本实例而言,INSERT ALL
和 INSERT FIRST
产生的结果相同,但它们仍然有差别。一旦 WHEN-THEN-ELSE
的结果为真,INSERT FIRST
会立即结束评估; INSERT ALL
则会逐一评估所有条件,而不论前面的测试结果是否真。因此,使用 INSERT ALL
有可能把同一行数据插入到多个表。
DB2
前面的 DB2 解决方案有点不太正统,因为它要求在目标表的定义里加入额外的约束条件,以确保查询结果的每一行都能被复制到正确的目标表中。该技巧的要点在于把数据插入一个针对所有目标表执行 UNION ALL
合并后得到的视图里去。如果为目标表添加的检查约束存在二义性(例如,多个表含有相同的检查约束),那么 INSERT
语句就无法判断要把数据插入到哪个表,这样它就无法成功执行。
MySQL、 PostgreSQL 和 SQL Server
只有 Oracle 和 DB2 可以使用一个语句就能把某个查询的结果集插入到多个目标表。