SQL Query for information *not* in database

Question

I have a large list of email addresses, and I need to determine which of them are not already in my database. Can I construct a query that will tell me this information?

I am using SQL Server 2000, if it requires non-standard extensions

Answer 1

make a temporary table, load the e-mailaddresses into the temporary table and then do a NOT IN query such as

SELECT emailaddress FROM temp_table WHERE emailaddress NOT IN (SELECT emailaddress FROM table)

you could extend that with an INSERT INTO

Answer 2

For a huge list, I would recommend loading that list into a second table (e.g., TEMP_EMAIL_ADDRESS), then use:

SELECT
  EMAIL
FROM
  TEMP_EMAIL_ADDRESS
WHERE
  EMAIL NOT IN (SELECT EMAIL FROM EMAIL_ADDRESS)

Data Transformation

If your data is in a text file named emails.txt (one row per line), you can create the insert statements using the following DOS command:

FOR /F %i IN (emails.txt) DO echo INSERT INTO TEMP_EMAIL_ADDRESS (EMAIL) VALUES ('%i') >> insert-email.sql

That command will create a new file called insert-email.sql in the current directory, containing all the inserts you need to inject the existing list of e-mail addresses into the database.