Db2 – Insert into DB2 table a result from temp table

db2insertquery

Is there a way to insert a temp table results to DB2 table, for example: 

WITH TEMP1 AS (
SELECT CUSTOMER_ID
FROM T_CUSTOMER WHERE CUSTOMER_ID BETWEEN 100 TO 300
),
TEMP2 AS (
SELECT CUTOMER_ID, ORDER_ID
FROM TEMP1 T1
JOIN T_ORDER R1 ON T1.CUSTOMER_ID = R1.CUSTOMER_ID)

SELECT CUTOMER_ID, ORDER_ID
FROM TEMP2

I tried the below way, but it did not work:

INSERT * INTO T_STORAGE (CUTOMER_ID, ORDER_ID)
SELECT *
FROM TEMP2

I searched a lot but couldn't find any way. Only to modify my query without temp tables, but then the query will be very slow.

Best Answer

Since TEMP2 only exists in the scope of the statement, you can't reference it from another statement. You can insert directly like:

INSERT INTO T_STORAGE (CUSTOMER_ID, ORDER_ID)
WITH TEMP1 AS (
    SELECT CUSTOMER_ID
    FROM T_CUSTOMER WHERE CUSTOMER_ID BETWEEN 100 AND 300
), TEMP2 AS (
    SELECT CUSTOMER_ID, ORDER_ID
    FROM TEMP1 T1
    JOIN T_ORDER R1 
        ON T1.CUSTOMER_ID = R1.CUSTOMER_ID
)
SELECT CUTOMER_ID, ORDER_ID
FROM TEMP2

If you need to use the content of TEMP2 in several statements, you can declare a global temporary table and insert to that first.

I'm not sure why you need the CTE though, this should be equivalent:

INSERT INTO T_STORAGE (CUSTOMER_ID, ORDER_ID)
SELECT T1.CUSTOMER_ID, R1.ORDER_ID
FROM T_CUSTOMER T1
JOIN T_ORDER R1 
    ON T1.CUSTOMER_ID = R1.CUSTOMER_ID
WHERE T1.CUSTOMER_ID BETWEEN 100 AND 300
Related Question