October 28, 2008 at 12:37 pm
I have read several posts, but none address precisely what I am looking for. I have a table with a column of sequential numbers. There are gaps in the numbers where rows have been deleted. The posts I have read show how to find the first and last numbers in the gaps. I need to find all of the numbers in the gaps.
For example, my table contains the following numbers:
1, 2, 4, 9, 10, 14
I need to populate a second table with 3, 5, 6, 7, 8, 11, 12 and 13.
I have been told that I need to use a cursor, but everything I have read indicates there should be a way to do this without a cursor.
I am a programmer and can do this very easily in code, but I need this to be a database stored procedure.
I would be grateful for any help finding a resolution.
Jack
October 28, 2008 at 1:04 pm
Here is the jist of what you need. First, you need to create a Tally table in the database. It is simply a table of numbers from 1 to whatever (hopefully higher that you need for this process). Then you can use the following code inside a stored procedure:
insert into MissingNumbers
select
t.N
from
Tally t
left outer join YourTable yt
on (t.N = yt.SeqNum)
where
yt.SeqNum is null
October 28, 2008 at 1:41 pm
Search this site for Tally Table - it is exactly what you are looking for.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 28, 2008 at 2:50 pm
Thanks Lynn. The code did exactly what I needed it to do.
Jack
October 28, 2008 at 2:55 pm
Glad it worked. I'm not that familiar with SQL Server 2005 Compact Edition, so wasn't too sure what I could and couldn't do.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy