Beginner sql server question

[sezme]

I have a simple table that i want to insert a new record into

field 1 is the primary key
field 2 is the value

when i insert the record into field 2 how to i add a new primary key number into field 1 (im used to access where you can use autonumber)

primary key datatype is smallint

thanks in advance

 

[JamesLean]

You can use the IDENTITY property to generate an auto-incrementing value (like the autonumber in Access). Look it up in Books Online for more info.

--James

 

[sezme]

I have had a browse through deja ato try and get some answers - not much luck - i am a newbie )

do i have to set the database table field to a different datatype ?

 

[clayton74]

Ive got my field as a serial and put identity to yes, identity seed to 1 and identity increment to 1 as well. But with the identity set it should work with a smallint

Andy

 

[nigelrivett]

create table x
(
id int identity (1,1) not null ,
...
)

you can add the field by

alter table x add id int identity (1,1) not null

In enterprise manager (yuk) by selecting the identity property in table design.

This is the eqivalent of the autonumber feld in access.
It is not guaranteed to be unique as it will just take the next value from the current seed so always needs a unique index.
It will also not be consecutive as any insert failures will leave gaps (as well as deletes).


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.