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]

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.

Best, Hugo
--

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

[Geetha]

Thanks, Hugo!

Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<c5********************************@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.

Best, Hugo