March 19, 2003 at 1:40 pm
I have an smallint field which can have maximum of 6000 value.
Everytime I insert record I want to insert the next available number but not the max(field1) + 1.
I posted this question under Analysis also but later on I saw that I should have posted it here. Sorry for repitition.
Because I might be deleting some records so there would be some unused numbers between 1 - 6000 which were not allocated yet.
Is there any single command or I have to use cursor and check out the next value with previous value.
March 19, 2003 at 1:49 pm
I don't think that there is a function for that, I think you must use a cursor unless anyone else knows a way.
March 20, 2003 at 6:04 am
A cursor is one way of locating the first unused number. Another way is to use the powers of MSSQL and sets.
Try this code.
-- sample table
create table sample ( range int, descr char(20))
-- load with 1,000 initial values
insert sample select anum, right(str(anum+100000),5) from
(
select hun.col1 * 100 + ten.col1 * 10 + unit.col1 as anum
from
( select (1) as col1 union select 2 union select 3 union select 4 union select 5
union select 6 union select 7 union select 8 union select 9 union select 0 ) unit
cross join
( select (1) as col1 union select 2 union select 3 union select 4 union select 5
union select 6 union select 7 union select 8 union select 9 union select 0 ) ten
cross join
( select (1) as col1 union select 2 union select 3 union select 4 union select 5
union select 6 union select 7 union select 8 union select 9 union select 0 ) hun
) a
-- make some vacanies in the number sequence
delete sample where range % 15 = 0
delete sample where range % 23 = 0
-- show the data we have
select * from sample order by range
-- locate the first space
declare @nextpos int
select top 1 @nextpos = max(b.range) + 1
from sample a join sample b on a.range > b.range
group by a.range
having a.range <> max(b.range) + 1
order by a.range
print 'inserting at ' + str(@nextpos)
-- use the located number to insert a new record
insert sample values (@nextpos, 'new insert 1')
-- repeat the locate
select top 1 @nextpos = max(b.range) + 1
from sample a join sample b on a.range > b.range
group by a.range
having a.range <> max(b.range) + 1
order by a.range
print 'inserting at ' + str(@nextpos)
-- insert another 'new record'
insert sample values (@nextpos, 'new insert 2')
-- end script
March 20, 2003 at 1:04 pm
Great!!
Thanks!!
March 21, 2003 at 12:14 pm
If you had a static table (Numbers6000) that held all the numbers from 1 through 6000, you could easily find the lowest missing number in YourTable:
select min(Number)
from Numbers6000
where Number not in (Select Field1 from YourTable)
Alternately you could generate the numbers on the fly:
declare @NextNum int
select @NextNum = min(Thousand + Hundred + Ten + Unit)
from (
select 0 as Unit union
select 1 union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9) as Units
cross join
(
select 0 as Ten union
select 10 union
select 20 union
select 30 union
select 40 union
select 50 union
select 60 union
select 70 union
select 80 union
select 90) as Tens
cross join
(
select 0 as Hundred union
select 100 union
select 200 union
select 300 union
select 400 union
select 500 union
select 600 union
select 700 union
select 800 union
select 900) as Hundreds
cross join
(
select 0 as Thousand union
select 1000 union
select 2000 union
select 3000 union
select 4000 union
select 5000 union
select 6000) as Thousands
where Thousand + Hundred + Ten + Unit <> 0
and Thousand + Hundred + Ten + Unit
not in (select Field1 from YourTable)
print '@NextNum = '
print @NextNum
A tip of the hat to Itzak Ben-Gan for pointing this technique out.
HTH
Steve Hendricks
MCSD, MCDBA
AFS Consulting Group
(949) 588-9800 x15
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15
March 21, 2003 at 2:15 pm
I used your example and it exactly fits into it. The one you used with sets.
Even you are right I could create one table which can have all the possible numbers and then do select command.
But as this case is in lot of fields so it is good to use set instead of creating tables for each field.
Thanks!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply