T-SQL alternative to NOT IN

[maxwell2323]

Can you help me with an alternative that might be more efficient and quicker than what I have in these few lines. I am inserting some records from a temp table into another table that already has records, and I have just one condition. Here is my code:

Insert into #MainTable
Select * from #Results3 as R3
Where R3.PatGuid NOT IN
(Select Pat.Guid from #MainTable)

--Thanks !!!!!!!!!

 

[markros]

Your code is already OK. If you use SQL 2008, then I suggest to look into MERGE operator in Help.

Alternative query may be not exists query also.

PluralSight Learning Library

 

[bborissov]

Insert into #MainTable
Select * 
       from #Results3 as R3
LEFT JOIN #MainTable ON  R3.PatGuid = #MainTable.PatGuid
WHERE #MainTable.PatGuid IS NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[drlex]

Borislav,
I tend to use the format in your example (left join) - do you think this is faster than a NOT EXISTS or NOT IN? I've never got around to testing such, but am curious.
Thanks & happy Friday!
lex

soi là, soi carré

 

[markros]

Most likely it will not be faster, but slower.
Gail Shaw wrote a series of blogs on this topic, take a look

http://sqlserverpedia.com/blog/sql-server-bloggers/left-outer-join-vs-not-exists/

PluralSight Learning Library

 

[drlex]

markros,
Thanks - I should have addressed my post to you also!

soi là, soi carré