Sql auto counter

[Deam]

I had converted some records in a SQL Server database - now I need to assign their ID fields numbers starting from 3100 to 3999. I know I have to do the Select part but how do I start numbering them ? and stop at 3999.

Update Tbl_Emp
SET EmpID = ?????????????????
WHERE EmpID = NULL


Appreciate any help.

 

[jbenson001]

Not sure if this is the best way, but it worked for me. First create an identity column on your table. Then try this code. After that, you can drop the identity column.

select * into #temp
from tbl_emp
where empid is null

declare @i int
declare @inc  int
declare @maxinc int
declare @bsid int

set @i = (select count(*) from #temp)
set @inc = 3100
set @maxinc = 3999
--This is the identity column (bsid)
set @bsid = (select min(bsid) from #temp)

while  @i > 0 AND @inc < @maxinc 
   begin
      update tbl_emp
      set empid = @inc
      where bsid = @bsid

      set @inc = @inc + 1
      delete from #temp where bsid = @bsid
      set @bsid = (select min(bsid) from #temp)
      set @i = (select count(*) from #temp)
   end

drop table #temp

Jim