Get ids not in table from large list of ids

Question

I have a large list of IDs that I need to check against a table to see wich onec that are not in my table.

I wrote this query:

SELECT id 
FROM ( VALUES (1,2,3,4...) AS Checking (id)
       WHERE id NOT IN (SELECT UniqueActivityID 
                          FROM UserActivity
                         WHERE CONVERT(DATE,[Date]) > CONVERT(DATE,'2015-06-24')
     )

The problem is that the query times out because of the large amount of numbers (20'000) and/or table size (+2 million rows). Can I do this in a better, less damanding way?

That is not even valid syntax. Even this is not valid syntax. SELECT id FROM ( VALUES (1,2,3,4) AS Checking (id) ) — paparazzo, Jun 25 '15 at 16:52
What datatype is your "Date" column? It would be better if you gave it a meaningful name instead of a datatype. What date is it? Such a generic name is like having a column named "int". — Sean Lange, Jun 25 '15 at 16:53
Are you really hardcoding 20,000 numbers in a table constructor??? Why not use a tally table? — Sean Lange, Jun 25 '15 at 16:53
You could use [the answer given here](http://stackoverflow.com/questions/1389605/sql-find-missing-ids-in-a-table#1389648) to get a table of IDs missing from your table, then check that against your list. — Mark, Jun 25 '15 at 16:59
@AmeyaDeshpande: No, The sql query is executed from c# where the id's come from. — Johan Ketels, Jun 26 '15 at 06:49
@SeanLange: Regarding the dates, all I can say is, I know. I'll deferentially look into the tally table solution. — Johan Ketels, Jun 26 '15 at 06:50
You can read about the tally table here. http://www.sqlservercentral.com/articles/T-SQL/62867/ You never did answer the question about what datatype your "Date" column is. — Sean Lange, Jun 26 '15 at 13:31
I had an answer if you would have just taken the effort to post valid syntax — paparazzo, Jun 26 '15 at 14:20
@blam The query given above works for me, therefore I assume it is valid. — Johan Ketels, Jun 29 '15 at 09:18
Really VALUES (1,2,3,4...) AS Checking (id) works for you? See my first comment. — paparazzo, Jun 29 '15 at 09:28
I'm sorry. SELECT id FROM ( VALUES (1),(2),(3),(4)) AS Checking (id)... — Johan Ketels, Jun 29 '15 at 11:51

score 0 · Answer 1 · answered Jun 25 '15 at 16:57

The except keyword is your friend.

 where somefield in 
 (select somefield
 from whereever
 where some conditions are met

 except 

 select somefield
 from whereever
 where the same conditions are met
 and more conditions are met that make you want to exclude the record)

Get ids not in table from large list of ids

1 Answers1