if object_id('tb')is not null drop table tb go create table tb(ID int identity(1,1),[Name] varchar(10)) insert tb select 'A' insert tb select 'B' insert tb select 'C' alter table tb add IDnew int go update tb set idnew=id*2+3 go alter table tb drop column ID exec sp_rename 'tb.idnew','ID','column' go select * from tb /*注意: 變更物件名稱的任何部分將會破壞指令碼 (Script) 和預存程序 (Stored Procedure)。 column 重新命名成 'ID'。 Name ID ---------- ----------- A 5 B 7 C 9 */
那你试着用语句做吧,下面是刚写的简单例子,你按你的需求改一下就好了:use tempdb gocreate table test ( id int identity(1,1), name char(1) ) go insert test select 'a' union all select 'b' goselect id = 1 * id,name into test_tmp from test gotruncate table test goupdate test_tmp set id = id * 2 + 3 goset identity_insert test on goinsert test(id,name) select id,name from test_tmp goset identity_insert test off goselect * from test go/** 5 a 7 b **/drop table test_tmp drop table test go
?
go
create table tb(ID int identity(1,1),[Name] varchar(10))
insert tb select 'A'
insert tb select 'B'
insert tb select 'C'
alter table tb
add IDnew int
go
update tb set idnew=id*2+3
go
alter table tb
drop column ID
exec sp_rename 'tb.idnew','ID','column'
go
select * from tb
/*注意: 變更物件名稱的任何部分將會破壞指令碼 (Script) 和預存程序 (Stored Procedure)。
column 重新命名成 'ID'。
Name ID
---------- -----------
A 5
B 7
C 9
*/
企业管理器的自己找一找吧, 没有环境
gocreate table test
(
id int identity(1,1),
name char(1)
)
go
insert test select 'a'
union all select 'b'
goselect id = 1 * id,name into test_tmp from test
gotruncate table test
goupdate test_tmp
set id = id * 2 + 3
goset identity_insert test on
goinsert test(id,name) select id,name from test_tmp
goset identity_insert test off
goselect * from test
go/**
5 a
7 b
**/drop table test_tmp
drop table test
go
如果不是自增列,就用下面的方法修改
update table set id=id*2+3
右击自增字段 → 选择修改(Modify)
→ Identity Specification(自增指定)项
→ 将Is Identify 修改为 No → 保存设置Ⅲ
UPDATE Ta SET ID = ID * 2 + 3Ⅳ
右击自增字段 → 选择修改(Modify)
→ Identity Specification(自增指定)项
→ 将Is Identify 修改为 Yes → 保存设置----Ⅰ查看含自增列的表
SELECT OBJECT_NAME= OBJECT_NAME(OBJECT_ID),Column_NAME = NAME FROM sys.columns
WHERE COLUMNPROPERTY(OBJECT_ID,name,'IsIdentity') > 0