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?