From: "C. Mason" Subject: Re: create table question Date: 1998/10/05 Message-ID: #1/1 Sender: usenet@news.arco.com X-Nntp-Posting-Host: 136.226.104.82 References: <6v3lap$mur$1@nnrp1.dejanews.com> <36156bc7.677624@192.86.155.100> Organization: ARCO Alaska, Inc. Newsgroups: comp.databases.oracle.server,comp.databases.oracle.tools X-Mimeole: Produced By Microsoft MimeOLE V4.72.3110.3 Try the following: create table table_name as select 0 f1,f2 from other_table; alter table table_name modify (f1 default 0); Thomas Kyte wrote in message <36156bc7.677624@192.86.155.100>... >A copy of this was sent to badstreetboy@my-dejanews.com >(if that email address didn't require changing) >On Fri, 02 Oct 1998 22:50:00 GMT, you wrote: > >>I want to create a table that has 2 fields: >>1st field: f1, integer, default 0 >>2nd field: f2, integer, copy exact rows from other table >>so that I will have a table like: >>0, aaa >>0, bbb >>... >> >>I used to do this in Ingres: >>create table table_name as select int4(0) as f1, f2 from other_table; >> >>How to do this in Oracle? >> >>Thanks!! >> >>-----------== Posted via Deja News, The Discussion Network ==---------- >>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own > >You could do this as a create table as select followed by an alter table (or as >a create table with a DEFAULT clause followed by an insert into select)... > > >SQL> create table table_name > 2 as select 0 f1, dummy f2 from dual; > >Table created. > >SQL> alter table table_name modify f1 default 0; > >Table altered. > >SQL> select * from table_name; > > F1 F >---------- - > 0 X > >SQL> insert into table_name (f2) values ('Y'); > >1 row created. > >SQL> select * from table_name; > > F1 F >---------- - > 0 X > 0 Y > >Thomas Kyte >tkyte@us.oracle.com >Oracle Government >Herndon VA > >-- >http://govt.us.oracle.com/ -- downloadable utilities > >--------------------------------------------------------------------------- - >Opinions are mine and do not necessarily reflect those of Oracle Corporation > >Anti-Anti Spam Msg: if you want an answer emailed to you, >you have to make it easy to get email to you. Any bounced >email will be treated the same way i treat SPAM-- I delete it.