-1

I saw a lot of examples online, but I'm still unsure on how to create a temporary table. The following example is how I create a temporary table in SQL Server:

if object_id('tempdb.dbo.#processid') is not null truncate table #processid
if object_id('tempdb.dbo.#processid') is not null drop table #processid

select distinct
    lot_id,
    ssr.run_oid,
    process_id
into #processid
from sigma.sigma_run ssr
inner join sigma.sigma_lot ssl on ssl.run_oid = ssr.run_oid

How can I create a temporary table just like this in Oracle SQL?

MT0
  • 143,790
  • 11
  • 59
  • 117
sheesh
  • 37
  • 5

1 Answers1

2

In Oracle, we usually do NOT create temporary tables.

Basically, it depends on what you want to do.

If you must, then you could e.g.

create global temporary table temp_table as
select distinct
    lot_id,
    ssr.run_oid,
    process_id
from sigma.sigma_run ssr
inner join sigma.sigma_lot ssl on ssl.run_oid = ssr.run_oid;

But, once again, you should explain what's behind the scene so that we could suggest what to do next.

Note that data in a temporary table is visible only to you; other users won't see anything. If you want to share data collected with that query, then create a "normal" table (i.e. remove global temporary from that statement).

Also, you could create a view (which is just a stored query) and then fetch data from it.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • 1
    SQL Server doesn't use them either, somewhat lazy developers do. A table generated with arbitrary fields and sizes and no keys or indexes is *always* a bad idea. – Panagiotis Kanavos Dec 21 '22 at 09:19
  • @Littlefoot Thank you for the reply! I need to create a temporary table cause my colleague wants me to connect it to Tableau with another code that I've done in SQL server. That was what he suggested it to me. – sheesh Dec 21 '22 at 09:28
  • @PanagiotisKanavos, thank you - fixed that. Sheesh, I added some more info - have a look, please. – Littlefoot Dec 21 '22 at 09:35
  • @PanagiotisKanavos - SQL Server temp tables ***can*** be indexed and constrained. – MatBailie Dec 21 '22 at 09:39
  • @MatBailie the question uses `SELECT INTO` so the table will start without indexes and constraints. Even with `INSERT INTO` though the table is created in `tempdb` which is a very busy database. – Panagiotis Kanavos Dec 21 '22 at 09:52
  • @Littlefoot I didn't mean that the answer is wrong. Using temporary tables as a crutch is wrong. In fact, the very example [Oracle docs use](https://docs.oracle.com/cd/E18283_01/server.112/e17120/tables003.htm#i1006400) (airline reservations) is *extremely bad and cringeworthy* - I work for an online travel agency and the thought of having millions of temporary tables per day....... – Panagiotis Kanavos Dec 21 '22 at 09:59
  • Absolutely, @Panagiotis; the fact that temporary tables exist doesn't mean that they should be (ab)used. My previous comment was related to my own sentence about MS SQL Server; I don't use it so my "knowledge" (ha-ha) about it is mostly gathered by reading various forums' discussions; that's where I came to know about close connection between terms "MS SQL Server" and "temporary tables". That's why I decided to remove that line. Thanks again! – Littlefoot Dec 21 '22 at 10:54
  • @Littlefoot I tried your method but I think its saying inappropriate INTO. What is it cause by? – sheesh Dec 22 '22 at 00:50
  • Ah, sorry, Sheesh; remove INTO (see fixed code). It is inappropriate in this context (I just copy/pasted code you posted, without checking it properly). INTO clause is used in PL/SQL, as - in Oracle - you have to select result of the SELECT statement *into* something (a local variable, collection, whatever). – Littlefoot Dec 22 '22 at 06:05