how do i set the auto sequence number in a sql table?

Add an IDENTITY column.  Like:  

CREATE TABLE Table1(Table1ID INT IDENTITY(1,1))

Greg

Hi shwelosa,

if we are talking about oracle then we have sequence instead of identity column, and we get next value from the sequence, so we will do something like following

-- Sequence
create sequence identity_seq
minvalue 1
maxvalue 9999999
start with 1
increment by 1
cache 5
order;
 
-- insert in table
insert in to test_table (number_col) values (identity_seq.nextval);

Select allOpen in new window

Hi Nouman,

I am talking abt identity column where i can choose to put a yes in indentity and increment by 1.  my problem is that after i do it in the design function in the table, i cannot see how to start the numbering the records in the table.

When you insert data in the table, don't insert data into the identity column.  It will automatically insert the next incremented number.

Greg

Greg is right, If you are working in sql server or access then when you insert in the table you will not pass any value in the identity column. It will be automatically filled. Thanks.

I'm sorry, but the value in the field remains a zero.  It is now an int, should it be defined as something other than an integer?  Thanks.

Why don't you start by telling us the database and post some relevant code you are using?  Few of us have crystal balls here.

Sorry.... I'm using Microsoft sql server 2000.  I created a table and i want to make one of the fields (label entry#)  the unique and primary key and it should automatically add 1 when a new record is created.

Setting the label entry# field as a unique and primary key will not auto-increment the field.  If you are doing this through the GUI, you have to set the IDENTITY specification to yes and set the increment and seed to 1.

Greg

Yes, i have done that.  However, when i look at the records in the table, the field entry# is still zero (0).  Should i delete all the records from the table and re-imported them?  what should i set the field, entry#, as (int, varchar, char, numeric, etc....?  Thnx again.