Select into temp table

Question

how to perform temp table in oracle from below script?

I tried usingselect * into #temp but it does not work. Please assist since i am new in oracle.

select * into temp from
(SELECT 
CASE WHEN Aaddress = '16' THEN 'A'
ELSE 'OTHERS'
END AS PRODUCT
FROM NAME
WHERE name.INACTIVE_CODE IN ('1', '2'))

Possible duplicate of [How do you create a temporary table in an Oracle database?](https://stackoverflow.com/questions/2671518/how-do-you-create-a-temporary-table-in-an-oracle-database) — wolfrevokcats, Mar 06 '18 at 00:56
In Oracle, it's not common practice to store results in temporary tables. What are you going to do with the results of your query? It's possible you don't need to store them at all! — Boneist, Mar 06 '18 at 12:02

Gordon Linoff · Answer 1 · 2018-03-06T01:06:15.253

0

Oracle uses create table as:

create table temp as
    SELECT (CASE WHEN Aaddress = '16' THEN 'A' ELSE 'OTHERS' END) AS PRODUCT
    FROM NAME
    WHERE name.INACTIVE_CODE IN ('1', '2');

Note if either Aaddress or INACTIVE_CODE are numbers, then the constants used for the comparisons should not have single quotes. Don't compare numbers to strings.

In SQL Server, #temp is a temporary table, but temp is not. In Oracle, you need to explicitly declare a temporary table:

create global temporary table temp as

edited Mar 06 '18 at 01:06

answered Mar 06 '18 at 01:00

Gordon Linoff

1,242,037
58
646
786

Isn't it better to create the GTT in advance, and then just use a INSERT INTO? – Alex Zen Mar 06 '18 at 10:41
@AlexZen . . . Then you need to define all the columns, which can be rather cumbersome. – Gordon Linoff Mar 06 '18 at 13:23
I see, you can use that for creating the table the first time. But still, you would just use "insert into" later, for achieving what OP asked. That's what I was pointing out, you don't need to create the GTT every time you need to use it – Alex Zen Mar 06 '18 at 14:03
@AlexZen . . . I don't know how the OP's code really works. `SELECT INTO` in SQL Server always creates the table. – Gordon Linoff Mar 06 '18 at 22:44

score 0 · Answer 2 · answered Mar 06 '18 at 11:04

Temporary tables in Oracle are created in advance, so that the definition is known before you use them.

So you could do

create global temporary table temp on commit preserve rows as select ... from ...

but this is not a recommendation to do this every time you run the query. More typical usage is specify the definition once:

create global temporary table temp (
  col1 ...
  col2 );

and then use INSERT to populate the table as required. By default, ie, as per my latter 'create' statement above, the moment you commit, the rows are lost. If you want to retain the rows after a commit, you add the 'on commit preserve rows' as per my first example. The reason you would have to do this in a create-table-as-select scenario is that otherwise your table would be created, populated with rows, and then immediately emptied as the command completes.

Select into temp table

2 Answers2