help with delete where not in query
- by kralco626
I have a lookup table (##lookup). I know it's bad design because I'm duplicating data, but it speeds up my queries tremendously. I have a query that populates this table
insert into ##lookup select distinct col1,col2,... from table1...join...etc...
I would like to simulate this behavior:
delete from ##lookup
insert into ##lookup select distinct col1,col2,... from table1...join...etc...
This would clearly update the table correctly. But this is a lot of inserting and deleting. It messes with my indexes and locks up the table for selecting from.
This table could also be updated by something like:
delete from ##lookup where not in (select distinct col1,col2,... from table1...join...etc...)
insert into ##lookup (select distinct col1,col2,... from table1...join...etc...) except if it is already in the table
The second way may take longer, but I can say "with no lock" and I will be able to select from the table.
Any ideas on how to write the query the second way?
