Unanswered: query insert and predicate on the same object
I've a querie which inserts into a table, while joining on it. Something like:
insert into table1
where columnA NOT EXIST(SELECT 1 from table1,table2 where table1.columnA!=table2.columnA)
As a rule of thumb, I avoid subselects and negative logic. However, sql2k seems to pick the fastest execution plan using this query, using a left anti semi join on that subselect. My question is this; what are the repercussions of inserting into a table when sql2k has already picked a query plan based on its stats? Is deadlocking possible? I haven't been able to find any evidence one way or the other. My objects are several million rows, so I am expecting some sort of side effect.
Any help would be greatly appreciated.
There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST