Script/procedure to Generate Sequential ID's in an ID Field

[ZKramer]

I created a new table and imported same into my SQLSVR 2000 database. Is there a script/procedure in the archives to generate a sequential ID for populated rows before I turn on the auto identity property ??

[JBane]

-- Using a test table:

create table tablename
(identitycolumn int,
othercolumn char(10))

-- Insert some data but not into the eventual identity column:

insert into tablename
(othercolumn)
values
('abcdefg')

insert into tablename
(othercolumn)
values
('abcdefg')

insert into tablename
(othercolumn)
values
('abcdefg')

insert into tablename
(othercolumn)
values
('abcdefg')

-- use a cursor and positioned updates to populate the empty identitycolumn values, start the @counter variable at the desired seed:

declare @counter int
select @counter = 1
declare idn cursor for
select * from tablename for update
open idn
fetch next from idn
while @@fetch_status = 0
begin
update tablename
set identitycolumn = @counter where current of idn
select @counter = @counter + 1
fetch next from idn
end

close idn
deallocate idn

-- View results:
select * from tablename



Jeff

[ZKramer]

Jeff,
Thank you, thank you, for your response.
I sure am glad I registered to the forum yesterday. I will be here often.
I am willing and want to learn as much as I can.

Joe