DB2 using With clause inside insert-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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;