DB2 Insert Into Temp Table in the middle of a script

Question

If I have several temp tables, and I want to insert data into one of them but then continue to create more temp tables is there a way to do that or do I need to break my query up.

This is an example of what I'd like to do

WITH A AS (SELECT Column1, Column2, Column3 FROM SomeTable WHERE SomeConditionA), -- Inital Temp Table
B AS(SELECT Column1, Column2, Column3 FROM SomeTable WHERE SomeConditionB), --Second Temp Table
INSERT INTO A SELECT * FROM B --Insert Table B into Table A
C AS(...), --Derives From A
D AS(...), --Derives From C
E AS(...) --Derives From D
SELECT * FROM E --Final Select Statement

Essentially my issue is that I don't know how to write the insert in a way that will allow me to continue creating more temp tables afterwards.

Thanks!

Charles · Accepted Answer · 2021-05-11T14:45:25.263

0

A Common Table Expression (CTE), WITH A AS (SELECT...) is not an actual temporary table, you can't insert into it.

It doesn't appear that you need to have a B since it's the same columns as selected in A, simply OR the conditions

WITH A AS (SELECT Column1, Column2, Column3 
           FROM SomeTable 
          WHERE SomeConditionA 
             or SomeConditionB),
  C AS(...), --Derives From A
  D AS(...), --Derives From C
  E AS(...) --Derives From D
SELECT * FROM E --Final Select Statement

If B needed to select other columns or from another table, then UNION ALL (if duplicates are ok or not possible) or UNION (to remove any duplicate rows) the results.

WITH A AS (SELECT Column1, Column2, Column3 
           FROM SomeTable 
          WHERE SomeConditionA 
            UNION ALL
          SELECT Column4, Column5, Column6 
           FROM SomeTable 
          WHERE SomeConditionB),
  C AS(...), --Derives From A
  D AS(...), --Derives From C
  E AS(...) --Derives From D
SELECT * FROM E --Final Select Statement

edited May 11 '21 at 14:45

answered May 11 '21 at 14:39

Charles

21,637
1
20
44

Thanks, the examples provided are much simpler than my actual query so the unions will not work, so the answer seems to be it cannot be done. I believe the solution then is to create multiple queries. – AdamTheD May 11 '21 at 14:54
If the "insert" would have worked, then the union would also. – Charles May 11 '21 at 14:57
Okay let me try it actually. – AdamTheD May 11 '21 at 14:59
Then have the union query transform them so they are compatible.... Same as you'd have had to do to insert them into A. – Charles May 11 '21 at 15:00

DB2 Insert Into Temp Table in the middle of a script

1 Answers1