数据插入数据库时自动填充空白ID
例如已经有了ID: 1,3,6,8,9
ID主键,现在为自动增长，
能否实现在插入时id=2 4,5
[解决办法]
SET IDENTITY_INSERT
允许将显式值插入表的标识列中。
语法
SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON
[解决办法]
OFF }
参数
database
是指定的表所驻留的数据库名称。
owner
是表所有者的名称。
table
是含有标识列的表名。
注释
任何时候，会话中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON。如果某个表已将此属性设置为 ON，并且为另一个表发出了 SET IDENTITY_INSERT ON 语句，则 Microsoft® SQL Server™ 返回一个错误信息，指出 SET IDENTITY_INSERT 已设置为 ON 并报告此属性已设置为 ON 的表。
如果插入值大于表的当前标识值，则 SQL Server 自动将新插入值作为当前标识值使用。
SET IDENTITY_INSERT 的设置是在执行或运行时设置，而不是在分析时设置。
权限
执行权限默认授予 sysadmin 固定服务器角色和 db_owner 及 db_ddladmin 固定数据库角色以及对象所有者。
[解决办法]
--> 测试数据：[ta]
if object_id('[ta]') is not null drop table [ta]
create table [ta](id int primary key)
insert [ta]
select 3 union all
select 5 union all
select 6 union all
select 9
select * from [ta]
--> 测试数据：生成连续数据表
if object_id('[tb]') is not null drop table [tb]
select identity(int,1,1) as id into tb from sysobjects a,sysobjects b
insert into ta select min(b.id) from tb b where not exists(select 1 from ta where b.id=id)
insert into ta select min(b.id) from tb b where not exists(select 1 from ta where b.id=id)
select * from ta
/*
id
-----------
1
2
3
5
6
9
(6 行受影响)
create table tb(id int , name varchar(10))
insert into tb values(1,'1')
insert into tb values(3,'3')
insert into tb values(6,'6')
insert into tb values(8,'8')
insert into tb values(9,'9')
go
--1
insert into tb
select min(m.id) + 1 ,'第1次' from
(select * , px = (select count(1) from tb where id < t.id) + 1 from tb t) m,
(select * , px = (select count(1) from tb where id < t.id) + 1 from tb t) n
where m.px = n.px - 1 and m.id <> n.id - 1
select * from tb order by id
/*
id name
----------- ----------
1 1
2 第1次
3 3
6 6
8 8
9 9
（所影响的行数为 6 行）
*/
--2
insert into tb
select min(m.id) + 1 ,'第2次' from
(select * , px = (select count(1) from tb where id < t.id) + 1 from tb t) m,
(select * , px = (select count(1) from tb where id < t.id) + 1 from tb t) n
where m.px = n.px - 1 and m.id <> n.id - 1
select * from tb order by id
/*
id name
----------- ----------
1 1
2 第1次
3 3
4 第2次
6 6
8 8
9 9
（所影响的行数为 7 行）
*/
--3
insert into tb
select min(m.id) + 1 ,'第3次' from
(select * , px = (select count(1) from tb where id < t.id) + 1 from tb t) m,
(select * , px = (select count(1) from tb where id < t.id) + 1 from tb t) n
where m.px = n.px - 1 and m.id <> n.id - 1
select * from tb order by id
/*
id name
----------- ----------
1 1
2 第1次
3 3
4 第2次
5 第3次
6 6
8 8
9 9
（所影响的行数为 8 行）
*/
drop table tb
go
set identity_insert tb off ;
go
create trigger tr_tb on tb
instead of insert
as
set nocount on ;
begin
declare @ID int
set identity_insert tb on ;
select top 1 @ID=t.ID+1 from tb t where not exists(select 1 from tb where ID=t.ID+1)
insert tb(ID,Name)
select @ID,Name from inserted
set identity_insert tb off;
end
go
insert tb select '插入2'
insert tb select '插入4'
select * from tb
idname
11
2插入2
33
4插入4
66
88
99
