Select * into removes defaults

[Geetha]

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]

Re: Select * into removes defaults

On 21 Oct 2004 12:57:02 -0700, Geetha wrote:
[color=blue]
>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.[/color]

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.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Geetha]

Re: Select * into removes defaults

Thanks, Hugo!

Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message news:<c56gn0ds6 cak3rri8ng8ohev 8onu251in9@4ax. com>...[color=blue]
> On 21 Oct 2004 12:57:02 -0700, Geetha wrote:
>[color=green]
> >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.[/color]
>
> 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.
>
> Best, Hugo[/color]