Home » SQL & PL/SQL » SQL & PL/SQL » Create Table
Create Table [message #351362] Tue, 30 September 2008 08:46 Go to next message
mhakimjavadi
Messages: 37
Registered: July 2008
Member
Hi,

I am trying to create a table which has some files with same type and size of the other table's fields. So I need to create table with those field type. Could you please tell me the format for create this table.

Thanks
Re: Create Table [message #351367 is a reply to message #351362] Tue, 30 September 2008 09:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
create table <new table> as select <some fields> from <other table> where 1=2

Regards
Michel
Re: Create Table [message #351368 is a reply to message #351362] Tue, 30 September 2008 09:15 Go to previous messageGo to next message
mhakimjavadi
Messages: 37
Registered: July 2008
Member
Thanks for your reply. However I need to create table without select statmnet. Please advice.

Thanks
Re: Create Table [message #351369 is a reply to message #351368] Tue, 30 September 2008 09:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why can't you use the SELECT statement?
It's the simplest and quickest way to do it.
Re: Create Table [message #351370 is a reply to message #351362] Tue, 30 September 2008 09:20 Go to previous messageGo to next message
mhakimjavadi
Messages: 37
Registered: July 2008
Member
You are rigth but I need to create the table and that should be empty for use. Then they will update that from oracle form.

Thanks
Re: Create Table [message #351375 is a reply to message #351370] Tue, 30 September 2008 09:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The statement provided will create an empty table - the "WHERE 1=2" guarantees that there will be no data in the table.

You're not planning to create these tables dynamically (ie from within an application) are you? Because you should know tha tin Oracle, that's almost always the wrong answer.
Re: Create Table [message #351378 is a reply to message #351362] Tue, 30 September 2008 09:50 Go to previous messageGo to next message
mhakimjavadi
Messages: 37
Registered: July 2008
Member
Good, I just found out that I can create table with select statmnet for most fields but I have some fields which are not in select statmnet and those will update laster. Please advice.

Thanks
Re: Create Table [message #351383 is a reply to message #351370] Tue, 30 September 2008 10:15 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
You can add some new columns via:
create table <new table> as select <some fields>, 0 <num_col1>, 'X' <char_col2> from <other table> where 1=2

But this is only usefull in some cases, because You can't explizit define the exakt TYPE of the new column and so it may be You have to use
ALTER TABLE <alter_table_clause>

anyway.

[Updated on: Tue, 30 September 2008 10:24]

Report message to a moderator

Re: Create Table [message #351384 is a reply to message #351383] Tue, 30 September 2008 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You can't explizit define the exakt TYPE of the new column

This is not correct, you can always CAST the value (including NULL) to the type you want.

Regards
Michel
Re: Create Table [message #351385 is a reply to message #351383] Tue, 30 September 2008 10:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Yes you can:
SQL> conn scott/tiger
Connected.
SQL> create table emp2 as select empno, cast('x' as varchar2(10)) as extra_col from emp where 1 = 2;

Table created.

SQL> desc emp2
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- ----------------
 EMPNO                                                                               NOT NULL NUMBER(4)
 EXTRA_COL                                                                                    VARCHAR2(10)

SQL> create table emp3 as select empno, cast('x' as varchar2(5)) as extra_col from emp where 1 = 2;

Table created.

SQL> desc emp3
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- ----------------
 EMPNO                                                                               NOT NULL NUMBER(4)
 EXTRA_COL                                                                                    VARCHAR2(5)



[Edit: ok, Michel beat me to it Smile ]

[Updated on: Tue, 30 September 2008 10:25]

Report message to a moderator

Re: Create Table [message #351387 is a reply to message #351384] Tue, 30 September 2008 10:27 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
@Michel You are right as usual, thanks for the clue!

[Updated on: Tue, 30 September 2008 10:28]

Report message to a moderator

Re: Create Table [message #351395 is a reply to message #351378] Tue, 30 September 2008 11:17 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
mhakimjavadi wrote on Tue, 30 September 2008 20:20

but I have some fields which are not in select statmnet and those will update laster.



Use ALTER TABLE Statement to add the columns which are not there in your SELECT statement of your CREATE TABLE Statement.

However, my question to you remains the same as @JRowbottom's.

Are you trying to create the table dynamically?

Regards,
Jo
Re: Create Table [message #351397 is a reply to message #351362] Tue, 30 September 2008 11:26 Go to previous messageGo to next message
mhakimjavadi
Messages: 37
Registered: July 2008
Member
No I am not trying to create the table dynamically.
Re: Create Table [message #351399 is a reply to message #351397] Tue, 30 September 2008 11:35 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@mhakimjavadi,

mhakimjavadi wrote on Tue, 30 September 2008 21:56
No I am not trying to create the table dynamically.

Then there is no change in my answer. Go through the link posted in my previous reply.

Regards,
Jo
Re: Create Table [message #351402 is a reply to message #351399] Tue, 30 September 2008 11:57 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
joicejohn wrote on Tue, 30 September 2008 18:35
@mhakimjavadi,

mhakimjavadi wrote on Tue, 30 September 2008 21:56
No I am not trying to create the table dynamically.

Then there is no change in my answer. Go through the link posted in my previous reply.

Regards,
Jo

Didn't you see Michel's and my replies? Why do it in two steps when you can do it in a single step?
Previous Topic: Other Way on this
Next Topic: Select Date/Time past 00:00:00?
Goto Forum:
  


Current Time: Wed Dec 07 12:58:47 CST 2016

Total time taken to generate the page: 0.09969 seconds