Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Fill Holes in a Sequence

Posted on 2005-03-15
Medium Priority
657 Views
I have a database table, with a field that's has a unique set of numbers , a user can manually enters these values in the database. At the same time I generate a unique number if the user wants to use what I have generated .  so in short I am trying to fill the holes in a sequence. Does anyone know how I can go about this

example -- fields values in database
1
2
3
4
9
22
23
24

I need to generate a new number that can fill the holes in the sequence,  the new number I generate would be.
"5" , as this would be the first hole I found in the sequence. Does anyone know how I can go about this.
Tks
0
Question by:vbothello
• 3
• 2

LVL 70

Expert Comment

ID: 13548062
This should do it:

DECLARE @nextNum INT

SELECT @nextNum = MIN(seqNum)
FROM someTable st1
WHERE NOT EXISTS(
SELECT 1
FROM someTable st2
WHERE st2.seqNum = st1.seqNum + 1 )

Note: An index on the seqNum would be extremely helpful for this query.
0

LVL 70

Expert Comment

ID: 13548069
CORRECTION, D'OH:

DECLARE @nextNum INT

SELECT @nextNum = MIN(seqNum) + 1
FROM someTable st1
WHERE NOT EXISTS(
SELECT 1
FROM someTable st2
WHERE st2.seqNum = st1.seqNum + 1 )
0

LVL 23

Expert Comment

ID: 13548210
I suggest using a reference table to avoid execessive consumption of resources...

declare @var int, @store_total int
create table #table_count(field_name int)
set @store_total = select (count(field_name) + 1)  from table1
set @var1 = 0
while @var1 <= @store_total
begin
set @var1 = @var1 + 1
insert into #table_count(field_name int) values (@var1)
end

insert into table1(field_name) select min(#table_count.field_name) from #table_count where #table_count.field_name not in (select field_name from table1)

hope this helped...
0

LVL 23

Expert Comment

ID: 13548284
this table can either be temporary or definite.  If using definite table don't forget to take it of once you fill all holes  and rebuild indexes.
0

LVL 23

Accepted Solution

Racim BOUDJAKDJI earned 2000 total points
ID: 13548345
for above 1000 records to "fill up" use definite table.  To build it...run this once

declare @var int, @store_total int
create table table_count(field_name int)
declare @var int, @store_total int
create table table_count(field_name int)
set @store_total = select (count(field_name) + 1)  from table1
set @var1 = 0
while @var1 <= @store_total
begin
set @var1 = @var1 + 1
insert into table_count(field_name int) values (@var1)
end

create index index_field_name on table_count(field_name)

insert into table1(field_name) select min(table_count.field_name) from table_count where table_count.field_name not in (select field_name from table1)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
###### Suggested Courses
Course of the Month13 days, 4 hours left to enroll