SQL MERGE statement not case sensitive

Question

I have the following MERGE statement:

MERGE TargetTable t
USING SourceTable s ON (t.ID = s.ID)

WHEN MATCHED 
     AND EXISTS (SELECT s.Day, s.Date, s.Name
                 EXCEPT
                 SELECT t.Day, t.Date, t.Name)
    THEN UPDATE 
         SET t.Day = s.Day,
             t.Date = s.Date,
             t.Name = s.Name

WHEN NOT MATCHED BY TARGET
    THEN INSERT (ID, Day, Date, Name)
         VALUES (s.ID, s.Day, s.Date, s.Name);

There are more columns however I just shorten it for ease of reading.

So when I run this it works fine except: if I change the case of something in source table, it does not get updated in the target table. I believe this is solved by using COLLATE for UTF8 however cannot figure it out.

EDIT: What I mean by if I change something in the source table is: For testing purposes I will execute the MERGE, then change a varchar field but only the case of a letter. Then when I run MERGE again that change does not get propagated to the target table as it does not see that as a change IE it is case insensitive

Thanks

Answer 1

Use a case-sensitive collation to qualify your columns.

Suppose your varchar columns were defined with a case-insensitive collation SQL_Latin1_General_CP1_CI_AS (explicitly, or by way of default for the database). Your subquery with EXCEPT would become:

SELECT s.Day, s.Date, s.Name COLLATE SQL_Latin1_General_CP1_CS_AS
EXCEPT
SELECT t.Day, t.Date, t.Name COLLATE SQL_Latin1_General_CP1_CS_AS

The CS in the collation's name stands for case-sensitive. The CI for case-insensitive.