Finding missing records...

[jazerr]

This question was posed in this month's SQL Server mag and applies to some work I need to do, But the author of the article couldnt figure it out...

If you have a table with an integer column..for example

CREATE TABLE #Soap (nID int)
INSERT INTO #SOAP VALUES(1)
INSERT INTO #SOAP VALUES(2)
INSERT INTO #SOAP VALUES(10)
INSERT INTO #SOAP VALUES(50)

I want to find all the values that are not represented in the column, and are between the min and max values.

for example, the results here would be
3
4
5
6
7
8
9
11
.
.
.
49

I want to do this without a temp table.

Any good way of doing this??

 

[DangerPowers]

You already have a Temp Table #SOAP, but that can be any other table with the values or a table variable so as to avoid the temp table constraint.

Try this (uses #SOAP):
--create some base data
CREATE TABLE #Soap (nID int)
INSERT INTO #SOAP VALUES(1)
INSERT INTO #SOAP VALUES(2)
INSERT INTO #SOAP VALUES(10)
INSERT INTO #SOAP VALUES(50)

--declare our vars
declare @min int
declare @max int
declare @curr int
declare @newtable table (new_id int identity(1,1), junk varchar(1))

--get the min and max
select @min=min(nID), @max=max(nID)
from #Soap

set @curr=@min

--insert from min to max into table var
while @curr<@max
begin
insert into @newtable (junk) values ('j')
set @curr=@curr+1
end

--get all items not in the base table
select new_id
from @newtable
where new_id not in (select nID from #soap)

I tried a simple example and it seemed to work...

 

[jazerr]

See. I can do it no problem with a temp table as the intermediate step, but the problem they posed was to do it without...

 

[DangerPowers]

Technically, the @newtable is a table variable and is not a temp table...

However, without a temporary storage area here, I can only see using a cursor and looping through and checking each row for existence and building a string of items not in the list.

 

[jazerr]

I got the string already too. I have each value separated by a space. I don't know how to separate each value out into an actual recordset.

 

[JohnDTampaBay]

This seems to do the trick without a temp table or table variable - dynamic SQL instead. It isn't pretty code, but it works.

SET NOCOUNT ON
DECLARE @min int, @max int, @ctr int, @gap int, @sql varchar(8000)

SELECT @min = min(nID),
  @max = max(nID),
  @ctr = min(nID) + 1
FROM #soap

SET @sql = NULL

WHILE @ctr < @max
BEGIN
  SELECT @gap = @ctr FROM #soap
  WHERE @z NOT IN (SELECT nID FROM #soap)

  IF @gap IS NOT NULL
  BEGIN
    IF @sql IS NULL
      SET @sql = 'select ' + CAST(@gap AS varchar(3))
    ELSE
      SET @sql = @sql + ' union select ' + CAST(@gap AS varchar(3))
  END
  SELECT @ctr = @ctr + 1
END

EXEC (@sql)
SET NOCOUNT OFF

--Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) selected

 

[tlbroadbent]

Here is another solution without an additional temp table and without a cursor.

set nocount on
CREATE TABLE #Soap (nID int)
INSERT INTO #SOAP VALUES(1)
INSERT INTO #SOAP VALUES(2)
INSERT INTO #SOAP VALUES(10)
INSERT INTO #SOAP VALUES(50)

Select i1+i2
From
(Select 1 i1
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 10) As u1,
(Select 0 i2
Union Select 10
Union Select 20
Union Select 30
Union Select 40) As u2
Where i1+i2 Not In (Select nID From #soap)
order by 1

Drop table #soap Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.