--设置允许显式插入自增列
SET IDENTITY_INSERT tablename ON
--当然插入完毕记得要设置不允许显式插入自增列
SET IDENTITY_INSERT tablename OFF
create table temp
(
id int identity,
[name] varchar(100)
)
insert temp
select 'a' union all
select 'b'
--#1. 备份表
--#2. 把数据全部写到临时表
select [newid] = id + 1, [name] into #temp from temp
--#3. 清空原表
truncate table temp
--#4. 设置允许插入id
SET IDENTITY_INSERT temp ON
--#5. 插入数据
insert into temp(id, [name])
select * from #temp
--36. 设置不允许插入id
SET IDENTITY_INSERT temp OFF
--结果
select * from temp
SET IDENTITY_INSERT tbname ON
insert 操作...
SET IDENTITY_INSERT tbname OFF
