db2 equivalent of tsql temp table

Question

How would I do the following TSQL query in DB2? I'm having problems creating a temp table based on the results from a query.

SELECT 
COLUMN_1, COLUMN_2, COLUMN_3
INTO #TEMP_A
FROM TABLE_A
WHERE COLUMN_1 = 1 AND COLUMN_2 = 2

The error message is:

"Error: SQL0104N An unexpected token "#TEMP_A" was found following "". Expected tokens may include: ":". SQLSTATE=42601"

Answer 1

You have to declare a temp table in DB2 before you can use it. Either with the same query you are running:

DECLARE GLOBAL TEMPORARY TABLE SESSION.YOUR_TEMP_TABLE_NAME AS (
    SELECT COLUMN_1, COLUMN_2, COLUMN_3
    FROM TABLE_A
) DEFINITION ONLY

Or "manually" define the columns:

DECLARE GLOBAL TEMPORARY TABLE SESSION.YOUR_TEMP_TABLE_NAME (
     COLUMN_1 CHAR(10)
    ,COLUMN_2 TIMESTAMP
    ,COLUMN_3 INTEGER
) 

Then populate it:

INSERT INTO SESSION.YOUR_TEMP_TABLE_NAME
SELECT COLUMN_1, COLUMN_2, COLUMN_3
FROM TABLE_A
WHERE COLUMN_1 = 1
  AND COLUMN_2 = 2

It's not quite as straight-forward as in SQL Server. :)

And even though it's called a "global" temporary table, it only exists for the current session. Note that all temp tables should be prefixed with the SESSION schema. If you do not provide a schema name, then SESSION will be implied.

Answer 2

maybe the "with" clause is what you look for:

with TEMP_A as (
  SELECT COLUMN_1, COLUMN_2, COLUMN_3
  FROM TABLE_A
  WHERE COLUMN_1 = 1 AND COLUMN_2 = 2
)
-- now use TEMP_A
select * from TEMP_A