Recipe4.6.Inserting into Multiple Tables at Once

Recipe 4.6. Inserting into Multiple Tables at Once

Problem

You want to take rows returned by a query and insert those rows into multiple target tables. For example, you want to insert rows from DEPT into tables DEPT_EAST, DEPT_WEST, and DEPT_MID. All three tables have the same structure (same columns and data types) as DEPT and are currently empty.

Solution

The solution is to insert the result of a query into the target tables. The difference from "Copying Rows from One Table into Another" is that for this problem you have multiple target tables.

Oracle

Use either the INSERT ALL or INSERT FIRST statement. Both share the same syntax except for the choice between the ALL and FIRST keywords. The following statement uses INSERT ALL to cause all possible target tables to be considered:

 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

Insert into an inline view that performs a UNION ALL on the tables to be inserted. You must also be sure to place constraints on the tables that will ensure each row goes into the correct table:

 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, and SQL Server

As of the time of this writing, these vendors do not support multi-table inserts.

Discussion

Oracle

Oracle's multi-table insert uses WHEN-THEN-ELSE clauses to evaluate the rows from the nested SELECT and insert them accordingly. In this recipe's example, INSERT ALL and INSERT FIRST would produce the same result, but there is a difference between the two. INSERT FIRST will break out of the WHEN-THEN-ELSE evaluation as soon as it encounters a condition evaluating to true; INSERT ALL will evaluate all conditions even if prior tests evaluate to true. Thus, you can use INSERT ALL to insert the same row into more than one table.

DB2

My DB2 solution is a bit of a hack. It requires that the tables to be inserted into have constraints defined to ensure that each row evaluated from the subquery will go into the correct table. The technique is to insert into a view that is defined as the UNION ALL of the tables. If the check constraints are not unique amongst the tables in the INSERT (i.e., multiple tables have the same check constraint), the INSERT statement will not know where to put the rows and it will fail.

MySQL, PostgreSQL, and SQL Server

As of the time of this writing, only Oracle and DB2 currently provide mechanisms to insert rows returned by a query into one or more of several tables within the same statement.