Home » SQL & PL/SQL » SQL & PL/SQL » how to make table query in oracle (4 merged) (4.0.1.14)
how to make table query in oracle (4 merged) [message #678363] Wed, 27 November 2019 15:50 Go to next message
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 ?
how to make table query in oracle [message #678365 is a reply to message #678363] Wed, 27 November 2019 20:53 Go to previous messageGo to next message
kabina
Messages: 8
Registered: November 2019
Junior Member
what is syntex of make table query in oracle . I want same table fields after making table.
In sql

Select a, b, c......... into temp from tble1
how to make table query in oracle [message #678366 is a reply to message #678363] Wed, 27 November 2019 20:54 Go to previous messageGo to next message
kabina
Messages: 8
Registered: November 2019
Junior Member
what is syntex of make table query in oracle . I want same table fields after making table.
In sql

Select a, b, c......... into temp from tbl1
how to make table query in oracle [message #678367 is a reply to message #678363] Wed, 27 November 2019 20:55 Go to previous messageGo to next message
kabina
Messages: 8
Registered: November 2019
Junior Member
what is syntax of make table query in oracle . I want same table fields after making table.
In sql

Select a, b, c......... into temp from tbl1
Re: how to make table query in oracle [message #678369 is a reply to message #678367] Wed, 27 November 2019 23:25 Go to previous messageGo to next message
vippysharma
Messages: 46
Registered: May 2013
Location: New Delhi
Member
CTAS
Re: how to make table query in oracle [message #678370 is a reply to message #678369] Wed, 27 November 2019 23:53 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
> how to make table query in oracle (4.0.1.14)
I'm not sure what 4.0.01.14 is, but I do know that it is NOT the correct version of Oracle DB

Post results from SQL below

SELECT * FROM V$VERSION;
Re: how to make table query in oracle (4 merged) [message #678372 is a reply to message #678363] Thu, 28 November 2019 00:14 Go to previous messageGo to next message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Wed, 27 November 2019 19:15

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
...

And feedback if you want to continue to get help.

Re: how to make table query in oracle (4 merged) [message #678390 is a reply to message #678372] Fri, 29 November 2019 07:42 Go to previous messageGo to next message
Bill B
Messages: 1919
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 #678394 is a reply to message #678390] Fri, 29 November 2019 13:51 Go to previous messageGo to next message
kabina
Messages: 8
Registered: November 2019
Junior Member
Bill B wrote on Fri, 29 November 2019 07:42
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;
Thanks for your help. I want to know what is in your last code represents. what is 1 and 2?
where 1 = 2

Re: how to make table query in oracle (4 merged) [message #678395 is a reply to message #678394] Fri, 29 November 2019 14:12 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
kabina wrote on Fri, 29 November 2019 11:51
Bill B wrote on Fri, 29 November 2019 07:42
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;
Thanks for your help. I want to know what is in your last code represents. what is 1 and 2?
where 1 = 2

The new table gets created but the WHERE 1 =2 prevent any rows from going into new table.

Be aware that CTAS does NOT create any INDEX on new table.
Re: how to make table query in oracle (4 merged) [message #678396 is a reply to message #678394] Fri, 29 November 2019 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1 is the first positive integer and 2 is the second one.
What do you think "where 1 = 2" does?

Re: how to make table query in oracle (4 merged) [message #678397 is a reply to message #678396] Fri, 29 November 2019 14:23 Go to previous messageGo to next message
Bill B
Messages: 1919
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 Go to previous message
John Watson
Messages: 8084
Registered: January 2010
Location: Global Village
Senior Member
kabina wrote on Wed, 27 November 2019 21:50
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 ?
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.
Previous Topic: ORA 01847
Next Topic: date format
Goto Forum:
  


Current Time: Fri Dec 13 03:07:19 CST 2019