SET IDENTITY_INSERT ONInsert into...SET IDENTITY_INSERT OFF这样的?
select * into #temp from tb where id >=2 delete from tb where id >= 2 insert into tb(id,字段1,字段2) values('a','b') insert into tb select * from #temp
SET IDENTITY_INSERT 表名 ONupdate 表名 set id = id +1 where id >=2insert into 表名(字段1,字段2) select 'aa','aaa'SET IDENTITY_INSERT 表名 OFF
insert into #t select 字段1,字段2 from 表 where id>=2 insert into 表 values('ffff','gggg') insert into 表 select * from #t
create proc spinsertany as declare @col1 varchar(100), @col2 varchar(100) insert into 表1(字段1,字段2) values('nnn','nnn') select @col1=字段1 from 表1 where ID=2 select @col2=字段2 from 表1 where ID=2 update 表1 set 字段1=(select 字段1 from 表1 where ID=@count) where ID=2 update 表1 set 字段2=(select 字段2 from 表1 where ID=@count) where ID=2 update 表1 set 字段1=@col1 where ID=(select count(*) from 表1) update 表1 set 字段2=@col2 where ID(select count(*) from 表1) go 直接在表上添加行,添加后与要被插入的位置的那行的数据做交换
sorry~ 忘记了 忘改变量@count 了 把@count改成select count(*) from 表1
可以用存储过程来实现,方式如下: create table t ( id int identity(1,1), 字段1 nvarchar(100), 字段2 nvarchar(100) ) goinsert into t select 'DD','SS' union all select 'II','VV' union all select 'YY','WW' goselect * from t结果如下: id 字段1 字段2 1 DD SS 2 II VV 3 YY WW脚本如下: declare @index int --位置 set @index = 2 select * into #temp from t where id >=@index delete from t where id >= @index SET IDENTITY_INSERT t ON --指定位置插入 insert into t(id,字段1,字段2) values(@index,'a','b') SET IDENTITY_INSERT t off --把表t的开始值设置从@index之后开始 dbcc checkident('dbo.t',reseed,@index) insert into t select 字段1,字段2 from #temp select * from t drop table #temp 结果为: 1 DD SS 2 a b 3 II VV 4 YY WW
delete from tb where id >= 2
insert into tb(id,字段1,字段2) values('a','b')
insert into tb select * from #temp
insert into 表 values('ffff','gggg')
insert into 表 select * from #t
as
declare @col1 varchar(100), @col2 varchar(100)
insert into 表1(字段1,字段2) values('nnn','nnn')
select @col1=字段1 from 表1 where ID=2
select @col2=字段2 from 表1 where ID=2
update 表1 set 字段1=(select 字段1 from 表1 where ID=@count) where ID=2
update 表1 set 字段2=(select 字段2 from 表1 where ID=@count) where ID=2
update 表1 set 字段1=@col1 where ID=(select count(*) from 表1)
update 表1 set 字段2=@col2 where ID(select count(*) from 表1)
go
直接在表上添加行,添加后与要被插入的位置的那行的数据做交换
要递增,只有再建立临时表,做导入导出操作
把@count改成select count(*) from 表1
create table t
(
id int identity(1,1),
字段1 nvarchar(100),
字段2 nvarchar(100)
)
goinsert into t
select 'DD','SS'
union all select 'II','VV'
union all select 'YY','WW'
goselect * from t结果如下:
id 字段1 字段2
1 DD SS
2 II VV
3 YY WW脚本如下:
declare @index int --位置
set @index = 2
select * into #temp from t where id >=@index
delete from t where id >= @index
SET IDENTITY_INSERT t ON
--指定位置插入
insert into t(id,字段1,字段2) values(@index,'a','b')
SET IDENTITY_INSERT t off
--把表t的开始值设置从@index之后开始
dbcc checkident('dbo.t',reseed,@index)
insert into t select 字段1,字段2 from #temp
select * from t
drop table #temp
结果为:
1 DD SS
2 a b
3 II VV
4 YY WW