I need to insert counting value into a column called [count_id], which currently has a value of 0 for every record. The table has two other fields: [zip_code] and [criteria_value], the table is called [demographic_data].
Currently I am using the following script to update the column:
declare @count as integer
set @count = 0
while exists (Select * from data_transition..demographic_data where count_id = 0)
set @count = (@count + 1)
set count_id = @count
where count_id = 0
and zip_code in (select top 1 zip_code from demographic_data where count_id = 0)
I don't want to use the identity column in SQL server, because it will apply a continuous number immediatley upon inserting the data into the table. Then some calculations are done on the table removing rows, so the [count_id] is updated from 0 after the unwanted rows are removed, and it is desirable to have the [count_id] continuous, for reasons not important here.