-
Script/procedure to Generate Sequential ID's in an ID Field
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|