I found this example by Serge of a multi table insert in a thread from
2008:

WITH source(c1, c2, c3) AS (VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)),
ins1(d) AS (SELECT 1
FROM NEW TABLE(INSERT INTO T1(c1)
SELECT c1 FROM source
WHERE c3 < 5)),
ins2(d) AS (SELECT 1
FROM NEW TABLE(INSERT INTO T2(c2)
SELECT c2 FROM source
WHERE c3 BETWEEN 5 AND 8)),
ins3(d) AS (SELECT 1
FROM NEW TABLE(INSERT INTO T3(c3)
SELECT c3 FROM source
WHERE c3 > 8))
VALUES 1;

I've been able to successfully use this form on my DB2 9.1
installation, but I can't tell from the plan whether DB2 is scanning
the source multiple times, once per insert target, or just once. How
could I prove one or the other to myself? For a VALUES source it
doesn't matter much, but if the source is a complex, multi-table join
it would.

Thanks!

On 8/11/2010 7:54 PM, Doug B wrote:
source.
And yes it will do multiple passes.
If you want to do it in one pass you have two choices.
1. Use a dynamic compound
BEGIN ATOMIC
FOR myrow AS SELECT * FROM T DO
INSERT INTO T1 SELECT * FROM (VALUES (myrow.c1, ....))
AS S1(c1, ..) WHERE c3 < 5;
INSERT INTO T2 SELECT * FROM (VALUES (myrow.c1, ....))
AS S1(c1, ..) WHERE c3 BETWEEN 5 AND 8;
...
END FOR;
END

2. Build a union all view and insert into that.
CREATE VIEW v AS
SELECT * FROM T1 WHERE c3 < 5
UNION ALL SELECT .....

INSERT INTO v SELECT ....

It's been a long time since I have done insert through union all but it
should work.

The dynamic compound should be the fastest though.



--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

On Aug 12, 8:55*am, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:
Thanks for the reply, Serge. I had thought I'd proven to myself that
only one pass was occurring by doing a test using a SEQUENCE object in
the source/common table expression like so:

WITH
source AS(
SELECT NEXTVAL FOR my_schema.my_seq id
FROM sysibm.sysdummy1),
ins1(d) AS (
SELECT 1
FROM NEW TABLE(INSERT
INTO my_schema.table_001
SELECT *
FROM source
WHERE id between 0 and 999999999)
),
ins2(d) AS (
SELECT 1
FROM NEW TABLE(INSERT
INTO my_schema.table_002
SELECT *
FROM source
WHERE id between 0 and 999999999)
),
ins3(d) AS (
SELECT 1
FROM NEW TABLE(INSERT
INTO my_schema.table_003
SELECT *
FROM source
WHERE id between 0 and 999999999)
)
VALUES 1;

The 3 "target" tables each contained the same id value from the
sequence, not three consecutive ones, so my assumption was that only a
single pass against the source had happened.

On 8/12/2010 12:20 PM, Doug B wrote:
scanned three times.



--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab