alter table tab add newfield int update tab set newfield=old_identity_field alter table tab drop old_identity_field sp_rename 'tab.newfield','old_identity_field ','COLUMN'
--你是要修改自增号吗!SET IDENTITY_INSERT 表 ON go update 表 set 编号1=编号 go SET IDENTITY_INSERT 表 OFF
直接alter table yourtable alter column yourcolumn int 这样就可以拉。。
it seems impossible to drop an identity constraint using SQL, you can try to do it in the Enterprise Manager, but behind the scenes, it will be creating a new table, copying the data from the old table to the new table, dropping the old table, and renaming the new tableor try to add a column and drop the identity column and then rename the new column, for exampleuse tempdb go--drop table tempx --gocreate table tempx (id int identity(1,1), name varchar(10)) go insert into tempx values ('hello') goinsert into tempx values ('world') goselect * from tempx goalter table tempx add myid int goupdate tempx set myid = id goselect * from tempx goalter table tempx drop column id goselect * from tempx gosp_rename 'tempx.myid','id','COLUMN' go引用思归的 select * from tempx goinsert into tempx values ('hello',2) goselect * from tempx go
To: LoveSQL(ligg) 你的方法是不行的!
-- try test: create table t(id1 int identity,address varchar(20)) go insert t values('ningbo') go select * from t goalter table t add id2 int go update t set id2=id1 go alter table t drop column id1 go sp_rename 't.id2','id1','column' goselect * from t go drop table t
-- j9988的少了个column,应该是:alter table tab add newfield int update tab set newfield=old_identity_field alter table tab drop column old_identity_field go sp_rename 'tab.newfield','old_identity_field ','COLUMN'-- or:alter table tab add newfield int update tab set newfield=old_identity_field alter table tab drop column old_identity_field exec sp_rename 'tab.newfield','old_identity_field ','COLUMN'
go
update 表 set 编号1=编号
go
SET IDENTITY_INSERT 表 OFF
go--drop table tempx
--gocreate table tempx (id int identity(1,1), name varchar(10))
go
insert into tempx values ('hello')
goinsert into tempx values ('world')
goselect * from tempx
goalter table tempx add myid int
goupdate tempx set myid = id
goselect * from tempx
goalter table tempx drop column id
goselect * from tempx
gosp_rename 'tempx.myid','id','COLUMN'
go引用思归的
select * from tempx
goinsert into tempx values ('hello',2)
goselect * from tempx
go
你的方法是不行的!
create table t(id1 int identity,address varchar(20))
go
insert t values('ningbo')
go
select * from t
goalter table t add id2 int
go
update t set id2=id1
go
alter table t drop column id1
go
sp_rename 't.id2','id1','column'
goselect * from t
go
drop table t
update tab set newfield=old_identity_field
alter table tab drop column old_identity_field
go
sp_rename 'tab.newfield','old_identity_field ','COLUMN'-- or:alter table tab add newfield int
update tab set newfield=old_identity_field
alter table tab drop column old_identity_field
exec sp_rename 'tab.newfield','old_identity_field ','COLUMN'