SQL 多表插入

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 语句。除 ALLFIRST 关键字不同之外,二者的语法并无二致。下面使用 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

MySQLPostgreSQLSQL Server
在写作本书时,这些数据库尚不支持多表插入。

SQL 多表插入 扩展知识

Oracle

Oracle 的多表插入使用 WHEN-THEN-ELSE 子句逐行评估嵌套 SELECT 语句所返回的结果,并将数据插入到相应的表中。虽然就本实例而言,INSERT ALLINSERT FIRST 产生的结果相同,但它们仍然有差别。一旦 WHEN-THEN-ELSE 的结果为真,INSERT FIRST 会立即结束评估; INSERT ALL 则会逐一评估所有条件,而不论前面的测试结果是否真。因此,使用 INSERT ALL 有可能把同一行数据插入到多个表。

DB2

前面的 DB2 解决方案有点不太正统,因为它要求在目标表的定义里加入额外的约束条件,以确保查询结果的每一行都能被复制到正确的目标表中。该技巧的要点在于把数据插入一个针对所有目标表执行 UNION ALL 合并后得到的视图里去。如果为目标表添加的检查约束存在二义性(例如,多个表含有相同的检查约束),那么 INSERT 语句就无法判断要把数据插入到哪个表,这样它就无法成功执行。

MySQLPostgreSQL 和 SQL Server

只有 Oracle 和 DB2 可以使用一个语句就能把某个查询的结果集插入到多个目标表。

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程