DB2 using With clause inside insert

mastermistik 0
Viewed 62

I am trying to insert into multiple tables, but the same subselect should be used in the insert stataments. When I am using With Clause, it does not work

This is working

INSERT INTO TABLE_A (COL_1, COL2)
SELECT COL_1, COL_2 
FROM TABLE_A
JOIN ...
WHERE ...

This subselect should be used in other insert statements too..

  SELECT COL_1, COL_2 
  FROM TABLE_A
  JOIN ...
  WHERE ...

Try to solve it so but does not work, for the first insert

WITH TEMP AS (
SELECT COL_1, COL_2 
      FROM TABLE_A
      JOIN ...
      WHERE ...);

INSERT INTO TABLE_A (COL_1, COL2)
    SELECT COL_1, COL_2 
    FROM TEMP

How can I do this to use the same subselect for whole my insert statements?

db2 sql
2 Answers

You may obviously use some temporary table to store the same result set, bu you may insert into multiple tables with the same statement as well.

CREATE TABLE A AS (SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES) DEFINITION ONLY;
CREATE TABLE B AS (SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES) DEFINITION ONLY;

WITH 
  T AS (SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES)
, IA AS 
(
  SELECT *
  FROM NEW TABLE 
  (
    INSERT INTO A (TABSCHEMA, TABNAME)
    SELECT TABSCHEMA, TABNAME
    FROM T
    WHERE TABSCHEMA LIKE 'SYS%'
  )
)
, IB AS 
(
  SELECT *
  FROM NEW TABLE 
  (
    INSERT INTO B (TABSCHEMA, TABNAME)
    SELECT TABSCHEMA, TABNAME
    FROM T
    WHERE TABSCHEMA NOT LIKE 'SYS%'
  )
)
-- You may use whatever SELECT statement here instead like:
-- SELECT 1 FROM SYSIBM.SYSDUMMY1
-- You can't use "select into nowhere" in SP, so you need something
-- like below with some V_DUMMY variable declared
-- SELECT 1 INTO V_DUMMY FROM SYSIBM.SYSDUMMY1
SELECT 
  (SELECT COUNT (1) CNT FROM IA) AS A_CNT
, (SELECT COUNT (1) CNT FROM IB) AS B_CNT
FROM SYSIBM.SYSDUMMY1
A_CNT B_CNT
432 41 
SELECT COUNT (1) AS A_CNT FROM A
A_CNT
432 
SELECT COUNT (1) AS B_CNT FROM B
B_CNT
41

dbfiddle link

Mark Barinstein0

Should look like this:

INSERT INTO TABLE_A (COL_1, COL2)
WITH TEMP AS ( SELECT COL_1, COL_2
                 FROM TABLE_A
                 JOIN ...
                 WHERE ... )
SELECT COL_1, COL_2
 FROM TEMP;
jwbradley0
Related