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?

SQLMenace · Accepted Answer · 2010-05-27 18:14:54Z

1

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());

answered May 27, 2010 at 18:14

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe · Accepted Answer · 2010-05-27 18:35:39Z

1

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

answered May 27, 2010 at 18:35

Joe

42.9k20 gold badges109 silver badges129 bronze badges

Comments

Kyle Rosendo · Accepted Answer · 2010-05-27 18:13:45Z

0

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');

answered May 27, 2010 at 18:13

Kyle Rosendo

25.3k8 gold badges83 silver badges118 bronze badges

1 Comment

Kyle Rosendo Over a year ago

@SQL - I know, it's in there. Saw that shortly after I answered and clarified.

Collectives™ on Stack Overflow

SQL insert default value

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related