Select (Select Count(*) from Table where ID<=a.ID) as NewID,ID from Table a
create table a(id int,name varchar(10)) insert into a select 1,'a' union all select 2,'b' union all select 10,'c' union all select 11,'d'create table a1(id int identity primary key,name varchar(10)) set identity_insert a1 on insert a1(id,name) select * from a set identity_insert a1 off drop table a exec sp_rename N'a1',N'a' insert a values('e') select * from a 結果: id name ----------- ---------- 1 a 2 b 10 c 11 d 12 e
需要新增一個表a1,然後把a1重命名為a
select identity(int,1,1) as new ID,A.* into #temp from A select * from #temp
from Table a
insert into a select 1,'a' union all
select 2,'b' union all
select 10,'c' union all
select 11,'d'create table a1(id int identity primary key,name varchar(10))
set identity_insert a1 on
insert a1(id,name) select * from a
set identity_insert a1 off
drop table a
exec sp_rename N'a1',N'a'
insert a values('e')
select * from a
結果:
id name
----------- ----------
1 a
2 b
10 c
11 d
12 e
select * from #temp