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
5
Medium Priority
?
657 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
  • 3
  • 2
5 Comments
 
LVL 70

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 70

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

578 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