| select into query [message #348498] |
Wed, 17 September 2008 00:18  |
|
|
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  |
 |
Littlefoot
Messages: 21825
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 |
|
|
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 #348508 is a reply to message #348501] |
Wed, 17 September 2008 01:08 |
Frank
Messages: 7901
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 |
|
|
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 #348544 is a reply to message #348519] |
Wed, 17 September 2008 03:27 |
|
|
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
