Storing results into variable or table where I can use it later

Question

My SQL query returns 132 rows with 1 column, I want to store them for a later use.

I have been through many posts on StackOverflow. I am trying following but it wont help:

DECLARE @store
SET @store=(select paramkey from parameter.p1val2 where paramname = 'BANKHOLIDAYS' and paramkey LIKE 'FI%')

Is it so I have to use a temp table to store all rows ? so I tried following

CREATE TABLE temp_table select paramkey from parameter.p1val2 where paramname = 'BANKHOLIDAYS' and paramkey LIKE 'FI%'

IT gives following error

An unexpected token "CREATE TABLE temp_table" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.62.57 SQL Code: -104, SQL State: 42601

Br Sara

Based on the error code I am assuming this is IBM's [tag:db2]. [edit] the question tags accordingly if that is not correct. — Igor, Aug 30 '18 at 12:57
Possible duplicate of [db2 equivalent of tsql temp table](https://stackoverflow.com/questions/11422572/db2-equivalent-of-tsql-temp-table) — Eric Brandt, Aug 30 '18 at 12:59

score 1 · Answer 1 · answered Aug 30 '18 at 13:08

I was using db2 and following thing actually worked for me, thankyou everyone

CREATE TABLE temp_table (paramkey varchar(200))
insert into temp_table 

select substr(paramkey,3) from parameter.p1val2 where paramname = 'BANKHOLIDAYS' and paramkey LIKE 'FI%'

score 0 · Answer 2 · answered Aug 30 '18 at 12:52

0

Just use it like :

select paramkey INTO #temp_table 
from parameter.p1val2 where paramname = 'BANKHOLIDAYS' and paramkey LIKE 'FI%'

SELECT * FROM #temp_table

Use #temp_table later now.

answered Aug 30 '18 at 12:52

Praneet Nadkar

823
7
16

score 0 · Answer 3 · answered Aug 30 '18 at 12:54

0

For MySQL, it would be

CREATE TEMPORARY TABLE foo SELECT paramkey FROM ...
-- Now use it ...
DROP TEMPORARY TABLE foo;

answered Aug 30 '18 at 12:54

Ondra Žižka

43,948
41
217
277

score 0 · Answer 4 · answered Aug 30 '18 at 12:56

for mssql server temp table creation

  SELECT  * INTO ##tempTable FROM (select paramkey from parameter.p1val2
            where paramname = 'BANKHOLIDAYS' and paramkey   LIKE 'FI%')

for mysql temp table creation

    CREATE TEMPORARY TABLE temp_table
   (select paramkey from parameter.p1val2
    where paramname = 'BANKHOLIDAYS' and paramkey   LIKE 'FI%');

score 0 · Answer 5 · answered Aug 30 '18 at 12:58

In Oracle, you can use

CREATE TABLE tempTable as SELECT * FROM originTable WHERE x=y;

DB2 has something similar as well

CREATE TABLE tempTABLE AS (
    SELECT *
    FROM originTABLE WHERE x=y
) WITH DATA

Fairly sure other DBMS have that too with some syntax variations.

Storing results into variable or table where I can use it later

5 Answers5