0

using - SQL Server 2008 R2 - SQL server management studio query pane.
I have an excel spreadsheet containing over 150k unique id's for contacts in our dynamics 2011 database. I need to query all other records in the database that are not in the list of 150k. What is the best way to do this? The records that I need to query have no activity on them so I am struggling to work out how to pull them out. Is it possible to use a NOT IN on 150k items? Should I add the 150k ids into a temporary table and then use NOT IN temptable? or is there a better way

Caroline Allen
  • 183
  • 14
  • I'd rather say go for temporary table. Because, It'd be messy and hard to debug if you put in all 150K records in NOT IN. Second thing is performance with NOT IN. So, advise is 1) Create a temp table and load all the excel data. 2) Use WHERE NOT EXISTS between the table gves you a unqiue set of data which is not present in other table. Good luck!!! – Shaan Feb 26 '16 at 15:39

1 Answers1

0

You can use not in, but performance will likely be pretty bad.

You're better off doing a not exists or a left join filtering out unjoined results. You would want to pull the records you want to exclude into the DB somehow, either through an import into a table, or writing out a temporary/variable table for working. But anyways...

Example tables:

myRecordsToExclude
myTable

example: 

select t.*
from myTable t
where not exists (
    select 1
    from myRecordsToExclude e
    where t.id = e.id
)

or 

select t.*
from myTable t
left join myRecordsToExclude e on t.id = e.id
where e.id is null
Kritner
  • 13,557
  • 10
  • 46
  • 72