指定位置插入某一个记录，大家帮忙！
表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
