On 21 Oct 2004 12:57:02 -0700, Geetha wrote:

>I have a table, tbl1:
> create table tbl1 ([field1] [char] (16) NULL DEFAULT (' '),
>[field2] [char] (6) NULL DEFAULT (' ')).
>
>When I do a select * into tbl2 from tbl1, tbl2 does not have defaults.
> Is there any settings I have to keep on when I do a select * into?
>Any help will be appreciated.

Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<c56gn0ds6cak3rri8ng8ohev8onu251in9@4ax.com>...
> On 21 Oct 2004 12:57:02 -0700, Geetha wrote:
>
> >I have a table, tbl1:
> > create table tbl1 ([field1] [char] (16) NULL DEFAULT (' '),
> >[field2] [char] (6) NULL DEFAULT (' ')).
> >
> >When I do a select * into tbl2 from tbl1, tbl2 does not have defaults.
> > Is there any settings I have to keep on when I do a select * into?
> >Any help will be appreciated.
>
> Hi Geetha,
>
> SELECT ... INTO <tablename> creates a new table and fills it with the data
> returned by the SELECT statement (which might come from zero, 1 or many
> more tables). It doesn't define any constraints (like PRIMARY KEY, UNIQUE,
> FOREIGN KEY, CHECK, NOT NULL) or properties (like DEFAULT or IDENTITY) for
> the new table.
>
> You must either create the table with CREATE TABLE first, including all
> constraints and properties, then populate it with "INSERT INTO <tablename>
> (column list) SELECT ...", or you keep the "SELECT ... INTO <tablename"
> and use ALTER TABLE to add the constraints and properties.
>