SQL insert default value

Question

SQL Server 2000

Say if I have a table like

CREATE TABLE [Message] (
    [MessageIdx] [int] IDENTITY (1, 1) NOT NULL ,
    [Message] [varchar] (1024) COLLATE Latin1_General_CI_AS NOT NULL ,
    [column1] ... ,
    [column2] ... ,
    ... ,
    [ValidUntil] [datetime] NULL ,
    CONSTRAINT [PK_Message] PRIMARY KEY  CLUSTERED 
    (
        [MessageIdx]
    ) WITH  FILLFACTOR = 90  ON [PRIMARY] 
) ON [PRIMARY]
GO

Since there're too many columns, so I am trying to insert value without specify column names explicitly. I want to insert a new row with all columns except 'MessageIdx' and 'ValidUntil' not specified. Therefore, I definitely don't want to type all column names. I tried below statement but it causes error. How can I do that? Thanks.

insert into message values (DEFAULT,'blah',something, ..., DEFAULT);

EDIT: AFAIN, SQL 2005 server you can skip the identity column when inserting. So that will be

insert into message values ('blah',something, ..., DEFAULT);

But is there any work around for SQL server 2000?

Answer 1

Use getdate(), and for IDENTITY columns (if using uniqueidentifier which I see you're not), you can use newid(), and set them in the design-view of the table in the default value. After that you'd simply go:

INSERT INTO Message (Message) VALUES ('blah');

Answer 2

you have to specify column names if you use set identity_insert

but you can do this

set identity_insert caconfig..fxmessage on;
insert into message (MessageIdx,[Message],[ValidUntil) 
values (1,'blah',GETDATE());
set identity_insert caconfig..fxmessage off;

I assume what you really want is this, it will generate the identity value for you

 insert into message ([Message],[ValidUntil) values ('blah',GETDATE());

Answer 3

Why are you using identity_insert if you appear to want it to assign an identity?

Answer 4

Don't be lazy. Do it the correct way, which is to specify the column list (excluding the identity column).