27,579
社区成员
发帖
与我相关
我的任务
分享
--第一步 删除原来的id
alter table ta drop column id
--第二步 增加一个自增的id
alter table ta add id int identity(1,1) primary key
if object_id('ta')is not null
drop table ta
go
create table ta(id int ,nam int)
go
insert ta select
1,3 union all select
3,3 union all select
4,3 union all select
7,3 union all select
8,3 union all select
9,3 union all select
10,3 union all select
11,3 union all select
12,3
go
alter table ta add idd int identity(1,1)
alter table ta drop column id
exec sp_rename 'ta.idd','id'
select * from ta
/*
nam id
----------- -----------
3 1
3 2
3 3
3 4
3 5
3 6
3 7
3 8
3 9
*/
if object_id('ta')is not null drop table ta
go
create table ta(id int ,nam int)
go
insert ta select
1,3 union all select
3,3 union all select
4,3 union all select
7,3 union all select
8,3 union all select
9,3 union all select
12,3 union all select
15,3 union all select
16,3
go
declare @n int
set @n=0
alter table ta add nm int --临时增加1列
update ta set @n=1+@n,id=@n
alter table ta drop column nm --删除临时列
select * from ta
id nam
----------- -----------
1 3
2 3
3 3
4 3
5 3
6 3
7 3
8 3
9 3
(9 行受影响)
--(4)identity列与普通列的相互转换
---<1>indetity列转换为普通列
create table #tg
(
id int identity(1,1),
col1 int
)
insert into #tg select 1
union all select 2
union all select 3
alter table #tg
add col2 int
update #tg set col2=id
select * from #tg
alter table #tg
drop column id
select * from #tg
---<2>将普通列转换为identity列
create table #TY
(
col1 int,
col2 int
)
insert into #TY select 1,1
union all select 2,2
union all select 13,53
union all select 24,44
--铺助表
create table #TT
(
id int identity(1,1),
col1 int
)
set identity_insert #TT off
insert into #TT (id,col1) select col1,col2 from #TY
select * from #TT
insert into #TT select 46
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ws_hgo/archive/2009/02/06/3866850.aspx