Using MS SQL Advanced Server 2000
I currently have a query where I use "NOT IN" as part of the WHERE clause. This is probably inefficient since I have hundreds of items that are in the "NOT IN" statement. My query checks, on a daily basis, whether any new "items" have been added to the database and returns those items. Using a phone book as an example here's what I am doing:
I need to know any new phone numbers that are added to the phone book database. That way I can add the name and address. So I have a script:
select distinct phonenumber
from directory
where phonenumber not in ('5550001', '55500002', '5550003', '5550004', ..........)
When I get a new phone number, I add it to the NOT IN statement. I also then add it to the phonebook with the users name and address (remember, this is just a example - not what I am really doing).
Is there a more efficient way to compare what was in the database yesterday with what is in it today and tell me what is new today?
One option I thought of was make two temporary tables, one with the distinct phonenumbers from every day but today and one with the numbers from today and then compare the two. But I haven't quite figured that out yet (I'm new to SQL Server programming) and I'm not sure that's the most efficient way to do it.
By the way, I'm using T-SQL and this will end up as a job in Enterprise Manager.
-SQLBill
I currently have a query where I use "NOT IN" as part of the WHERE clause. This is probably inefficient since I have hundreds of items that are in the "NOT IN" statement. My query checks, on a daily basis, whether any new "items" have been added to the database and returns those items. Using a phone book as an example here's what I am doing:
I need to know any new phone numbers that are added to the phone book database. That way I can add the name and address. So I have a script:
select distinct phonenumber
from directory
where phonenumber not in ('5550001', '55500002', '5550003', '5550004', ..........)
When I get a new phone number, I add it to the NOT IN statement. I also then add it to the phonebook with the users name and address (remember, this is just a example - not what I am really doing).
Is there a more efficient way to compare what was in the database yesterday with what is in it today and tell me what is new today?
One option I thought of was make two temporary tables, one with the distinct phonenumbers from every day but today and one with the numbers from today and then compare the two. But I haven't quite figured that out yet (I'm new to SQL Server programming) and I'm not sure that's the most efficient way to do it.
By the way, I'm using T-SQL and this will end up as a job in Enterprise Manager.
-SQLBill