Oracle Syntax for SELECT INTO a Temp Table? 4

[kjv1611]

Is it possible to use a SELECT INTO statement in Oracle SQL and get the same/similar results as said statement in MS SQL? If not, then do I have to first create the table, and then use INSERT INTO?

Thanks for any info/references anyone can give me. I have practically no experience whatsoever on Oracle SQL, and I'm learning rather quickly that what I learned in MS SQL does not really carry over - some things, yes, but a lot - no.

 

[SantaMufasa]

KJV,

Oracle doesn't support the syntax, "TOP 100". For this, do you want the first 100 random rows from FancySystemTableOne, or do you want the first 100 rows from FancySystemTableOne based upon some order?

In either or your examples, above, you could have SELECTs that give you the "TOP n" rows. Here is an example from my EMP table:

create table emp2 as
       (select *
          from (Select * from emp
                 order by salary, last_name)
         where rownum <= 100
       );

Table created.

The above results in a table, EMP2, which contains the 100 top-paid employees.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[kjv1611]

Ah, yes, I forgot about the difference on TOP. I used that same syntax a couple of times in the past.

Thanks for putting it here, though, b/c this is going to be some kind of reference thread for me over the next couple of months!

 

[kjv1611]

<bonk head>
I just have to remember ROWNUM rather than ROWCOUNT.

For a while there (though I had already tested with this a while back a few times), I was putting ROWCOUNT in my queries instead of ROWNUM.

 

[Beilstwh]

One big difference between Microsoft and Oracle is you almost never need temporary tables, simply use a query, oracle takes care of everything. For example

select a.col1,b.col2
from
(select col1,junk
from my_other table
where col3 = 'A') a,
my_first_table b
where a.junk=b.junk;

is completely legal and does away with the need for a temporary table.


Bill
Lead Application Developer
New York State, USA