You have a simple mistake in your test:

SELECT key1, key2 FROM #tmp

EXCEPT

SELECT key1, key2 FROM #tmp2

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:

;WITH updates ({list of columns for the CTE here})

AS (

SELECT key1, key2 FROM #tmp

EXCEPT

SELECT key1, key2 FROM #tmp2

)

SELECT *

INTO #updates

FROM updates;

UPDATE alias

SET ...

FROM YourFinalTable t

INNER JOIN #updates u ON u.key1 = t.key1 ...

INSERT INTO YourFinalTable (...)

SELECT ...

FROM YourFinalTable t

LEFT JOIN #updates u ON u.key1 = t.key1 ...

WHERE u.key1 IS NULL;

If you only need the insert portion, then you don't need the #updates temp table and you could perform the INSERT directly with a join to your CTE.