Auto Number Sequence

How can I create a number sequence starting at a certain number and continue on for the number of records I have.

For example I have 3000 records in my table and a field named I created called RecordId which I'd like to start at number 1 and goto 3000 (or maybe even start at 9000 and goto 12000 or however many records there are).

In my pseudo SQL code Im guessing it would be something like...

select * from Incident

update Incident
set RecordId( i=9000; i<=Number of Records in Table; i++)

Whats the easiest way to do this?

--1.

Alter TABLE Incident

DROP COLUMN RecordId

--2.

Alter TABLE Incident

ADD RecordId int IDENTITY(3000,1)--if you want start at 3000

--or 3.

Alter TABLE Incident

ADD RecordId int IDENTITY(3000,1)--if you want start at 1

You can look up this infomation Alter Table and Alter Column from Books Online.

|||

hi,

you can use the identity function

make use use of its parameter seed=3000 to start from 3000

here's the syntaxt

identity(seed, increament)

here's your sample code

use northwind

select IDENTITY(int, 3000,1) AS ID_Num,
lastname,firstname into #temp from employees

select * from #temp

regards,

joey

|||A potentially more expressive function you could use in place of identity is the row_number.

select row_number over( partiton by ... order by ...) + startin_number,

One caveat of Row_Number implementation on SqlServer is that SS05 doesn't allow an empty order by, nor does it allow to sort on a constant.|||

hi if any body can tell me the solutions of my prob.

i have a table in Access in which prid is auto number , but the prob is that these prid is not in sequnce i.e, some number missing, like that after 8 its 12 , how will i arange it with delete data etc

|||If you have issues with this column only within this table, you can delete this column and recreate another Auto number field for your prid column in Access.|||

You can do this by combining a variable with an update statement.
Here's an example. If you run this whole set of code, the records in the table end up with values 1,2,3,4.....

-- create a simple test table
create table testcounter (thefld int)
go

-- add four rows, all with the same value
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
go

-- Declare and initialize an int variable
DECLARE @.thecount int
set @.thecount = 0

-- update the table using the variable.
update testcounter SET
@.thecount = @.thecount + 1,
thefld = @.thecount
from testcounter
go

-- list the results
select * from testcounter