?
Solved

Fill Holes in a Sequence

Posted on 2005-03-15
5
Medium Priority
?
654 Views
Last Modified: 2010-08-05
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
Comment
Question by:vbothello
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
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 69

Expert Comment

by:Scott Pletcher
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

by:Racim BOUDJAKDJI
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

by:Racim BOUDJAKDJI
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

by:
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)

then for your insert use...
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question