指定位置插入某一个记录，大家帮忙！
表A
id（自动id）name
1 a
2 b
3 c
现在 2 和3 之间插入一条记录（新3）旧3往下移动变成4
id（自动id）name
1 a
2 b
3 xx
4 c
怎么做？



[解决办法]
可以考思路

Insert A Select name From A Where ID = 3
Update A Set name = 'xx ' Where ID = 3
[解决办法]

create table T(id int identity(1,1),name varchar(100))
go
insert into T
select 'a ' union all
select 'b ' union all
select 'c '


select * from T


select *
into T2
from T
where id> =3

delete T where id> =3


dbcc checkident (T,reseed,2)

insert into T select 'xxx '

set identity_insert T on

insert into T(id,name) select ID+1,name from T2

set identity_insert T off


select * from T


drop table T,T2

[解决办法]
@pos --你要插入的位置
update 表A set id = id + 1 where id > = @pos
insert 表A values (@pos, 'xxx ')

[解决办法]

Create Table A
(idInt Identity(1, 1),
nameVarchar(10))
Insert A Select 'a '
Union All Select 'b '
Union All Select 'c '
GO
Insert A Select name From A Where ID = 3
Update A Set name = 'xx ' Where ID = 3

Select * From A
GO
Drop Table A
--Result
/*
idname
1a
2b
3xx
4c
*/
[解决办法]
-- 腾位置
UPDATE tb SET id = id + 1
WHERE id > = 3

-- 插入
INSERT tb (id, name)
VALUES(3, 'xx ')
[解决办法]
update 表A set id = id +1 where id > =2

insert into 表A(id,name) select 3, 'xx '
[解决办法]
那万一id是自增长的主键呢？
[解决办法]
楼上老大，ID是Identity类型的标识列，能update ?
[解决办法]
--如果ID是自动增长，按如下处理：

create table T(id int identity(1,1),name varchar(100))
go
insert into T
select 'a ' union all
select 'b ' union all
select 'c '


select * from T


select *
into T2
from T
where id> =3

delete T where id> =3


dbcc checkident (T,reseed,2)

insert into T select 'xxx '

set identity_insert T on

insert into T(id,name) select ID+1,name from T2

set identity_insert T off


select * from T


drop table T,T2