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 ??
Printable View
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 ??
-- 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
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