Home » SQL & PL/SQL » SQL & PL/SQL » select into query (Oracle 10g R2 , redh hat linux 4EL)
select into query [message #348498] Wed, 17 September 2008 00:18 Go to next message
er.tripathi
Messages: 6
Registered: September 2008
Location: Kathmandu , nepal
Junior Member

Dear all ,
select * from various_tables where ..... into TEMP tab1
select * from various_tables where ..... into TEMP tab2

then
select * from tab1 union all select * from tab2?

In oracle i can see "select into" is used to store subquery result in a varibale , what if I want to store result of sub query in a table you can say Global temporary table.

Pleas advice !
Re: select into query [message #348499 is a reply to message #348498] Wed, 17 September 2008 00:22 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that your previous database knowledge is different from Oracle. MS SQL Server, perhaps?

In Oracle, if you want to store result of a query into a table (doesn't matter which kind of a table), you'd use such a syntax:
CREATE TABLE some_table_name AS
  SELECT * FROM another_table
  WHERE your_condition_here


If that's an one-time-only task you have to do, fine. But, if you plan to spend some more time with Oracle, perhaps you'd want to get familiar with it. In that case, check the documentation; start with Concepts book; SQL Reference will certainly be interesting for you.

[EDIT: fixed the syntax]

[Updated on: Wed, 17 September 2008 00:23]

Report message to a moderator

Re: select into query [message #348501 is a reply to message #348499] Wed, 17 September 2008 00:32 Go to previous messageGo to next message
er.tripathi
Messages: 6
Registered: September 2008
Location: Kathmandu , nepal
Junior Member

Dear Friend ,

Here is what i am stuck with ,

select * from table1 ; i need to store result of this query in table1 say .
select * from tabl2; i need to store result of this query in table2 say ,

then

select * from table1 union all select * from table2 ??

how can do this please advice?

Re: select into query [message #348503 is a reply to message #348501] Wed, 17 September 2008 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"insert ... (select ...)"

Regards
Michel

[Updated on: Wed, 17 September 2008 00:54]

Report message to a moderator

Re: select into query [message #348508 is a reply to message #348501] Wed, 17 September 2008 01:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
er.tripathi wrote on Wed, 17 September 2008 07:32
Dear Friend ,

Here is what i am stuck with ,

select * from table1 ; i need to store result of this query in table1 say .
select * from tabl2; i need to store result of this query in table2 say ,

then

select * from table1 union all select * from table2 ??

how can do this please advice?



In Oracle, we don't store intermediate results. In Oracle the above typically is done in a single statement.
Re: select into query [message #348519 is a reply to message #348503] Wed, 17 September 2008 01:54 Go to previous messageGo to next message
er.tripathi
Messages: 6
Registered: September 2008
Location: Kathmandu , nepal
Junior Member

Dear Friend ,

Insert (select) works when Insert table has predefined structure .But in my case i need to insert result of subquery in table whose structure in unknown ;
any Idea ??
Re: select into query [message #348523 is a reply to message #348519] Wed, 17 September 2008 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In Oracle you don't create table on the fly as you don't create procedure code on the fly.
Don't use sql server/sybase way ot programming if you program for Oracle.

Regards
Michel
Re: select into query [message #348525 is a reply to message #348519] Wed, 17 September 2008 02:08 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
er.tripathi wrote on Wed, 17 September 2008 08:54
i need to insert result of subquery in table whose structure is unknown

Re-read code given in message #348499.
Re: select into query [message #348544 is a reply to message #348519] Wed, 17 September 2008 03:27 Go to previous messageGo to next message
er.tripathi
Messages: 6
Registered: September 2008
Location: Kathmandu , nepal
Junior Member

Dear friend ,
this is what we do in INFORMIX ,

1 Select name , address from table1 into temp tab1 ; In temp tablespace tab1 table is created on fly

2. Select * from table2 into temp tab2 ; In temp tablespace tab1 table is created on fly

3. select * from tab1 union all select * from tab2 ;

what is counter part available in oracle10g R2 ? What about temp tablespace in oracle ? I guess like informix , TEMP tablespace should have this provision other wise what the use of TEMP tablespace ?
Re: select into query [message #348548 is a reply to message #348544] Wed, 17 September 2008 03:41 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In Oracle there is no need to use temporary tables to achieve this.

You can just do
Select name , address from table1
union all
Select * from table2;

assuming that the set of columns from the two queries match up.
Previous Topic: how to retrieve date with timezone
Next Topic: Max(average sale) for any 3 parallel months
Goto Forum:
  


Current Time: Sat Dec 10 03:09:17 CST 2016

Total time taken to generate the page: 0.04936 seconds