how to make table query in oracle (4 merged) [message #678363] |
Wed, 27 November 2019 15:50 |
|
kabina
Messages: 8 Registered: November 2019
|
Junior Member |
|
|
There is query in SQL to make table. it will create new table with same fields as per below syntax.
Select ename, sal,....... into temp1 from emp
how we can write in oracle to make table ?
|
|
|
|
|
|
|
|
|
Re: how to make table query in oracle (4 merged) [message #678390 is a reply to message #678372] |
Fri, 29 November 2019 07:42 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
use the CTAS format (Create Table As Select)
create table my_table as
select * from my_other_table;
You can also use a where clause when making the table
create table my_table as
select * from my_other_table
where my_col = 'A';
And if you just want the table structure without any rows then
create table my_table as
select * from my_other_table
where 1 = 2;
[Updated on: Fri, 29 November 2019 07:43] Report message to a moderator
|
|
|
|
|
|
Re: how to make table query in oracle (4 merged) [message #678397 is a reply to message #678396] |
Fri, 29 November 2019 14:23 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
It's a trick so that the where clause will always be false so it makes the table but pulls in no rows. You would just as easy say
where 'A' = 'B'
where 'TRUE' = 'FALSE'
where 'APPLE' = 'PEAR'
because the where clause will always be false, the optimizer is smart enough to simply build the table and immediatly get out without checking any rows in the tabels
[Updated on: Fri, 29 November 2019 14:31] Report message to a moderator
|
|
|
Re: how to make table query in oracle (4 merged) [message #678398 is a reply to message #678363] |
Sat, 30 November 2019 02:11 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
kabina wrote on Wed, 27 November 2019 21:50There is query in SQL to make table. it will create new table with same fields as per below syntax.
Select ename, sal,....... into temp1 from emp
how we can write in oracle to make table ? By "SQL" do you mean "SQL Server"? If so, you are probably looking for a Private Temporary Table. For example:orclz>
orclz> create private temporary table ora$ptt_emp_dept
2 as select ename,dname from emp natural join dept;
Table created.
orclz> select * from ora$ptt_emp_dept;
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
MILLER ACCOUNTING
KING ACCOUNTING
JONES RESEARCH
SCOTT RESEARCH
FORD RESEARCH
ADAMS RESEARCH
ALLEN SALES
TURNER SALES
JAMES SALES
WARD SALES
MARTIN SALES
BLAKE SALES
13 rows selected.
orclz> However, structures such as this are often a Bad Thing. I (along with many others) suspect that temporary tables are often used by SQL Server developers do not understand that Oracle manages read consistency very differently.
|
|
|