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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply