Jeffrey Williams-493691 (10/25/2010)
You have a simple mistake in your test:
SELECT key1, key2 FROM #tmp
EXCEPT
SELECT key1, key2 FROM #tmp2
Sadly, that wasn't a mistake, that's actually what I'm trying to do, and the system doesn't like the idea. I only want to except on some of the key columns, but return all the results from the first set that aren't in the second.
I didn't thoroughly go through your code, but if all you are trying to do is a UPDATE/INSERT, then you could do something like: (snip code)
Yes, but I've found that using the cte like that actually decreases the performance of the result, since now it not only has to do the anti semi-join, but it has to re-integrate the results as well. Have you noticed different results? I admit to not having a breadth of experience with the syntax but a couple of 500k row tables I'm working against as a test subject.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA