请教 如何从空白编号插入

Question

我对ID字段设置了自动编号,以后插入的时候可以不用输入ID,但是问题是,加入我删除了ID1的记录,那么我剩下2345,插入的时候就自动把ID编号成了6,请问如何能从小于当前ID最大值前未使用的ID进行插入呢,谢谢

Answer 1

--如果想进行断号处理,则不能用自增,用如下的存储过程实现.
--以下三段,存储过程一样,只是显示不同数据时的情况.
--这是没有数据时.
create table tb(id int)
gocreate proc my_proc @id int OUTPUT
as
begin
  if not exists (select 1 from tb)
     set @id = 1
  else
     begin
       select @id = min(m.px) + 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.id <> n.id - 1 and m.px = n.px - 1
     if @id is null
        select @id = max(id) + 1 from tb
     end
   
end
godeclare @id as intexec my_proc @id outputselect @iddrop table tb
drop proc my_proc/*
            
----------- 
1（所影响的行数为 1 行）
*/--这是存在断号时.create table tb(id int)
insert into tb values(1)
insert into tb values(3)gocreate proc my_proc @id int OUTPUT
as
begin
  if not exists (select 1 from tb)
     set @id = 1
  else
     begin
       select @id = min(m.px) + 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.id <> n.id - 1 and m.px = n.px - 1
     if @id is null
        select @id = max(id) + 1 from tb
     end
   
end
godeclare @id as intexec my_proc @id outputselect @iddrop table tb
drop proc my_proc/*
            
----------- 
2
（所影响的行数为 1 行）
*/--这是不存在断号时.
create table tb(id int)
insert into tb values(1)
insert into tb values(2)gocreate proc my_proc @id int OUTPUT
as
begin
  if not exists (select 1 from tb)
     set @id = 1
  else
     begin
       select @id = min(m.px) + 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.id <> n.id - 1 and m.px = n.px - 1
     if @id is null
        select @id = max(id) + 1 from tb
     end
   
end
godeclare @id as intexec my_proc @id outputselect @iddrop table tb
drop proc my_proc/*
            
----------- 
3
（所影响的行数为 1 行）
*/

Answer 2

取消自增属性 
--创建测试表
CREATE TABLE t1(ID int IDENTITY,A int)
GO
--插入记录
INSERT t1 VALUES(1)
GO--1. 将IDENTITY(标识)列变为普通列
ALTER TABLE t1 ADD ID_temp int
GOUPDATE t1 SET ID_temp=ID
ALTER TABLE t1 DROP COLUMN ID
EXEC sp_rename N't1.ID_temp',N'ID',N'COLUMN'
INSERT t1 VALUES(100,9)
GO--2. 将普通列变为标识列
CREATE TABLE t1_temp(ID int,A int IDENTITY)
SET IDENTITY_INSERT t1_temp ON
INSERT t1_temp(ID,A) SELECT * FROM t1
SET IDENTITY_INSERT t1_temp OFF
DROP TABLE T1
GOEXEC sp_rename N't1_temp',N't1'
INSERT t1 VALUES(109999)
GO--显示处理结果
SELECT * FROM t1
/*--结果:
ID          A 
----------------- ----------- 
1           1
100         9
109999      10
--*/