dunkin1969
asked on
ORACLE How to SELECT INTO a TEMP TABLE
Hello,
I want to SELECT into a TEMP TABLE. The Temp table DOES NOT EXIST. Can I create a temp table on the fly?
For example:
SELECT *
INTO TEMP_TABLE (doesn't yet exist)
FROM....
WHERE....
IS this possible in Oracle SQL?
Thanks!
I want to SELECT into a TEMP TABLE. The Temp table DOES NOT EXIST. Can I create a temp table on the fly?
For example:
SELECT *
INTO TEMP_TABLE (doesn't yet exist)
FROM....
WHERE....
IS this possible in Oracle SQL?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Temp tables in Oracle don't work like some other databases.
There isn't a transient temp table that magically goes away when you no longer need it. Just the data.
You create the table, you must drop it when you are done.
>>I was trying to prevent having to create the tables manually first
You only create it once EVER. It is never dropped.
It isn't:
create table.
use table.
drop table.
It is:
create table.
use table FOREVER AND ALWAYS (never dropped).
There isn't a transient temp table that magically goes away when you no longer need it. Just the data.
You create the table, you must drop it when you are done.
>>I was trying to prevent having to create the tables manually first
You only create it once EVER. It is never dropped.
It isn't:
create table.
use table.
drop table.
It is:
create table.
use table FOREVER AND ALWAYS (never dropped).
ASKER
Thanks for the explanation Slightw.
I don't care if the temp table is dropped after my session ends. My purpose for using temp tables is to alleviate subqueries and make performance better on our slow server. I just need the temp tables for that session. Which is why I didn't need to create the table first (I hate having to do that just for something i only need on a temp basis)... especially if there are 100+ columns I'd have to create. Much easier to use the temp table method like in the SQL i'm used to:
SELECT *
INTO #Temp
FROM
(SELECT...)
But like I said, for my particular purpose, I will use the method suggested by Micheal74. Thank you
I don't care if the temp table is dropped after my session ends. My purpose for using temp tables is to alleviate subqueries and make performance better on our slow server. I just need the temp tables for that session. Which is why I didn't need to create the table first (I hate having to do that just for something i only need on a temp basis)... especially if there are 100+ columns I'd have to create. Much easier to use the temp table method like in the SQL i'm used to:
SELECT *
INTO #Temp
FROM
(SELECT...)
But like I said, for my particular purpose, I will use the method suggested by Micheal74. Thank you
>>I don't care if the temp table is dropped after my session ends.
You should. If you don't drop it, it will remain in the data dictionary forever.
You should. If you don't drop it, it will remain in the data dictionary forever.
ASKER
Thanks I think that will work for me. Even if the data is lost when session ends.
Slightw,
I assume you're saying to create my temp table first, then insert into it. I was trying to prevent having to create the tables manually first. I remember in SQL, all I had to do was SELECT INTO #TableName... I was hoping Oracle had something similar.
Thanks!