12

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"

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
I_AM_JARROD
  • 685
  • 2
  • 7
  • 20
  • If I run the error in DB2 i get the following error..."Error: SQL0104N An unexpected token "#TEMP_A" was found following "". Expected tokens may include: ":". SQLSTATE=42601" – I_AM_JARROD Jul 10 '12 at 23:07
  • As it turned out, I did not have permissions to create temp tables. – I_AM_JARROD Apr 23 '18 at 15:47

2 Answers2

17

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.

bhamby
  • 15,112
  • 1
  • 45
  • 66
  • 3
    A little helpful note: you might want to have "on commit preserve rows" included as "on commit delete rows" is the default and that behavior may not be desirable. – Richard Morgan Apr 22 '13 at 15:10
  • 1
    @RichardMorgan Or have everything run on the same Transaction. – bhamby Apr 24 '13 at 22:09
  • Does the first bit of code there just define the table or populate it? Why is there a select from table_a in the definition? – JSideris Apr 07 '14 at 04:08
  • 1
    @Bizorke the `SELECT` in the first statement just gets the definition of those columns, and makes the columns in the temp tables with the same names, data types, etc. In (my opinion) a bit of shortsightedness, IBM doesn't allow you to define and populate a temporary table at the same time. – bhamby Apr 07 '14 at 12:49
  • 1
    @Hogan he did say that he wanted a temp table based on a query you are running. However, I went ahead and edited in how you would do it "manually" – bhamby Nov 01 '19 at 16:13
  • I was able to create the temporary table, and then I tried to populate it, (I had a message "SQL executed OK. Number of rows affected=1"), but when I made a SELECT, my temporary table is empty. Could you know what is happening? – Ane Hervías Caballero Apr 21 '22 at 06:31
  • I have found it! I need a ON COMMIT PRESERVE ROWS in the definition of the table – Ane Hervías Caballero Apr 21 '22 at 06:59
4

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
Fred B.
  • 75
  • 5