Why does my insert not detect existing rows in my target table?

Question

I'm trying to implement SCD2 by using insert and update instead of using MERGE. I need to insert a new row into my target table if the matching ids have different hash values. The table contains id, name, hash value, and 1 as enabled, which entails that the rows is the most current version.

As of the moment, I'm not getting the expected output. For example, if I have the id “1” in both the target and source table but the hash value differs, it inserts the value if I run the query again into my target table, leaving me with the id “1” with many duplicate hash values.

Query:

INSERT INTO target
    SELECT s.ID, s.namn, s.hashh, 1 AS enablee
    FROM source s
    JOIN target t ON s.id = t.id 
    WHERE s.hashh <> t.hashh

Output:

1   demo    222 0
1   demo    22220
1   demo    222 1
2   demo2   666 1
2   demo2   666 1
2   demo2   888  1

Expected output:

1   demo    222 1
1   demo    22220
2   demo2   666 1
2   demo2   888 0

Ideally, I would like the insertion not to work and give me the output: (0 rows affected) if the hash value already exists in the targeted table.

Answer 1

To understand the behavior, consider than WHERE applies to SELECT statement, not INSERT.

You can just run

SELECT s.ID, s.namn, s.hashh, 1 AS enablee
FROM source s
JOIN target t ON s.id = t.id 
WHERE s.hashh <> t.hashh

to see what is inserted. The join finds all rows with same id, and mismatched hash. If all hashes match, it produces no result. But if there are some rows with mismatched hash, you get the results even if there is a matching row too.

What you need is the opposite, join on matched hash only and check if you found a match. Something like

SELECT s.ID, s.namn, s.hashh, 1 AS enablee
FROM source s
LEFT JOIN target t ON s.id = t.id 
    AND s.hashh = t.hashh
WHERE t.hashh IS NULL