Re: create table question

A copy of this was sent to badstreetboy_at_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_at_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.