November 11, 2008 at 9:20 pm
hi all,
i have a column which is primary key column, i have given two options to user 1. autocomplete 2.manual. It means user can give manual code or I will generate if user is not giving manual code, but I have a limitation till 99999. I mean the column contains a maximum value of 99999. Assume that user has given 99999 as manual code and a record is inserted with that code. So, next time when user has not given any code then i need to find out all the missing values of that column.
So that i can generate a code with minimum value and insert the record.
November 11, 2008 at 9:47 pm
Sounds like a good use of a Tally table. Read the following article, it should help you out. If not, let us know.
November 11, 2008 at 10:02 pm
Actually, there's a faster method than the Tally table for this...
SELECT TOP 1 (SELECT ISNULL(MAX(b.SerialNumber),0)+1
FROM #JbmTest b
WHERE b.SerialNumber < a.SerialNumber) AS AvailableNumber
FROM #JbmTest a
WHERE a.SerialNumber - 1 NOT IN (SELECT SerialNumber FROM #JbmTest)
AND a.SerialNumber - 1 > 0
--Jeff Moden
November 11, 2008 at 10:09 pm
Veterans Day, my brain is taking a slight break.
November 12, 2008 at 6:19 am
Heh... mine too... I copied it from a gap detecter that I had in my library. It's some really ugly code with the correlated sub-query and all, but it's amazingly fast. Some guy posted it on another forum about 5 years ago and I didn't comment his name into the code. I don't even remember which forum it was. I tweaked it a bit, but the original idea is his.
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply