转载：http://www.thinksaas.cn/topics/0/423/423869.html

Demo

/**************** 准备环境********************/--判断是否存在test表if object_id(N'test',N'U') is not nulldrop table test--创建test表create table test(id int not null,name varchar(20) not null)--插入临时数据insert into test values (1,'成龙')insert into test values (3,'章子怡')insert into test values (4,'刘若英')insert into test values (8,'王菲')select * from test/**************** 实现更改自动增长列********************/begin transactioncreate table test_tmp(id int not null identity(1,1),name varchar(20) not null)goset identity_insert test_tmp ongoif exists(select * from test)exec(' insert into test_tmp(id, name ) select id, name from test with(holdlock tablockx)')goset identity_insert test_tmp offgodrop table testgoexec sp_rename N'test_tmp' ,N'test' , 'OBJECT'gocommitGO/****************验证结果*****************/insert into test values ('张曼')select * from test

 

 

 

实例：

/**************** 实现更改自动增长列********************/begin transactioncreate table test_tmp(UserGradeID int not null identity(1,1),UserGrade nvarchar(8) not null,[Status] int not null,Remark nvarchar(128) ,adduser nvarchar(32) ,upduser nvarchar(32) ,addtime datetime2(7) ,updtime datetime2(7)  )goset identity_insert test_tmp ongoif exists(select * from m_usergrade)exec(' insert into test_tmp(UserGradeID,UserGrade,[Status],Remark,adduser,upduser,addtime,updtime ) select UserGradeID,UserGrade,[Status],Remark,adduser,upduser,addtime,updtime from m_usergrade with(holdlock tablockx)')goset identity_insert test_tmp offgodrop table m_usergradegoexec sp_rename N'test_tmp' ,N'm_usergrade' , 'OBJECT'gocommitGO