--create test environment create table #t1(id int,value varchar(20)) insert into #t1(id,value) select 1,'Test11' union all select 3,'Test12' union all select 5,'Test13' union all select 7,'Test21' union all select 9,'Test22' union all select 11,'Test23' union all select 14,'Test31' union all select 18,'Test32' union all select 40,'Test33'declare @i intselect @i=min(id)-1 from #t1 update #t1 set id=@i,@i=@i+1 select * from #t1--delete template table drop table #t1
select identity(int,1,1) NID ,ID into #temp from TableNameupdate TableName set ID=(select NID from #temp where ID=TableName.ID)drop table #temp
create table #t1(id int,value varchar(20))
insert into #t1(id,value)
select 1,'Test11'
union all select 3,'Test12'
union all select 5,'Test13'
union all select 7,'Test21'
union all select 9,'Test22'
union all select 11,'Test23'
union all select 14,'Test31'
union all select 18,'Test32'
union all select 40,'Test33'declare @i intselect @i=min(id)-1 from #t1
update #t1
set id=@i,@i=@i+1
select * from #t1--delete template table
drop table #t1
into #temp
from TableNameupdate TableName
set ID=(select NID from #temp where ID=TableName.ID)drop table #temp