SQL Query to report when item NOT found

[bmacbmac]

Hi, I have about 10,000 numbers to search in a database of about 100,000. I can write a simple statement to search for each number indivually... like this:

 select sdocumentno 
from documentinfo 
where sdocumentno = '12345'
or sdocumentno = '12346'
or sdocumentno = '12346'

Is there a way I can get a report for all of the numbers that are not found? I'm trying to find out which of my 10,000 numbers are not in the database.

Thanks,

Brian

 

[gmmastros]

Do you have these numbers in a table?

If you did, then you could simply do a left join while checking for nulls.

Ex:

Select TableOfNumbersToSearch.Field
From   TableOfNumbersToSearch
       Left Join OriginalTable
         On TableOfNumbersToSearch.IdField = OriginalTable.IdField
Where  OriginalTable.IdField Is NULL

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bmacbmac]

No, unfortunately I have them in a list in excel. I was just going to concatenate a statement from the list if it was the easiest way to go

 

[mhoyt]

Are your 10,000 numbers sequential?

If so, how about creating a table with all 10,000, then doing a join between that table and documentinfo so that a query returns only those records from your new table that do not have a match in documentinfo?

 

[mhoyt]

George has the right solution for you. To get an Excel list of numbers into a SQL table is easy, use the DTS Import wizard in Enterprise Manager, then use George's code

 

[gmmastros]

yep.

Open Enterprise Manager
Drill down to your database
Right Click -> All Tasks -> Import Data



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bmacbmac]

that worked perfectly.. thanks!