Efficient way to detect difference between tables in - EXCEPT not working

Question

I tried to use EXCEPT (not sure if best?):

IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1;
IF OBJECT_ID('tempdb..#t2') IS NOT NULL DROP TABLE #t2;

SELECT 
    *
INTO #t1
FROM 
(
    SELECT '001' AS id, '1' AS type, 40 AS cost
        UNION 
    SELECT '002' AS id, '2' AS type, 50 AS cost
) AS IQ1;

SELECT 
    *
INTO #t2
FROM 
(
    SELECT '001' AS id, '1' AS type, 40 AS cost
        UNION 
    SELECT '002' AS id, '2' AS type, 50 AS cost
        UNION 
    SELECT '003' AS id, '2' AS type, 50 AS cost
) AS IQ1;

SELECT * FROM #t1;
SELECT * FROM #t2;

SELECT * FROM #t1
    EXCEPT
SELECT * FROM #t2;

It appears not to be working. Any ideas? The use case is to efficiently detect new rows in #t2 but also difference such us:

SELECT '002' AS id, '2' AS type, 51 AS cost

Here 51 is different to 50 in #t1. Hope this makes sense?

Answer 1

I think you want to compare #t2 to #t1 as well. #t2 has more rows.

The EXCEPT operator will select all rows from the first table and remove all rows that are in the second table. So if #t2has more rows, you would never know.

  SELECT * FROM #t1
  EXCEPT
  SELECT * FROM #t2
  UNION ALL
  SELECT * FROM #t2
  EXCEPT
  SELECT * FROM #t1;