Problem: in mssql, to insert into a destination table, data from a source table - only data that is not already inserted.
1st solution, a simple query like [1]; but when you have to run for more data, this will go bad. And here is the solution:
When using querys in anotherr query, a good ideea in order to made a opitmized query is to use in subquery what you have to process, not what you want to exclude:
[1] Wrong:
insert into table_destination select * from table_source where identifierfield not in (select identifierfield from table_destination)
[2] To corect this, you have to change somehow the expression "where identifierfield not in" into a expression like "where identifierfield in". So, the optimized way of this query should look like:
insert into table_destination select * from table_source where identifierfield in (select identifierfield from table_source s left join table_destination d on d.identifierfield = s.identifierfield where d.identifierfield is null)