sql server: MERGE has unexpected results

Question

The way these rows usually come into the target table the first time are with a sparse number of columns populated with mostly text data with the remainder of the columns set to NULL. On subsequent passes, the fresh data populates existing known (non null) and unknown (NULL) data. I've ascertained that the fresh data ( #pld) do indeed contain different data. The data does not appear to change. Here's what I have:

  BEGIN TRANSACTION

  BEGIN TRY


  MERGE INTO [metro].listings AS metroList
    USING #pld as listnew
      ON metroList.id = listnew.id
      AND metroList.sid = listnew.sid
      WHEN MATCHED AND (
        metroList.User != listnew.User
         or metroList.Email != listnew.Email
         or metroList.LocName != listnew.LocName
   ) THEN
   UPDATE SET
    metroList.User = listnew.User,
    metroList.Email = listnew.Email,
    metroList.LocName = listnew.LocName,
   WHEN NOT MATCHED THEN
  INSERT
   ( User,
     Email,
     LocName
   )
   VALUES
   (
     listnew.User,
     listnew.Email,
      listnew.LocName
    );

  COMMIT TRANSACTION

 END TRY


 IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;

 END CATCH 

I've tried replacing the != to under the update portion of the statement with <> . Same results. This has to be related to a comparison of a possible (likely) null value against a string--maybe even another null? Anyway, I'm calling on all sql-geeks to untangle this.

Answer 1

Also you can use option with NULLIF() function.

NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.

WHEN MATCHED AND (
                  NULLIF(ISNULL(metroList.[User],''), listnew.[User]) IS NOT NULL
                    OR NULLIF(ISNULL(metroList.Email, ''), listnew.Email) IS NOT NULL
                    OR NULLIF(ISNULL(metroList.LocName, ''), listnew.LocName) IS NOT NULL
                  )
THEN

Answer 2

Comparing NULL with an empty string will not work.

If either side could be NULL, you could do something like:

WHEN MATCHED AND (
        COALESCE(metroList.User, '')    <> COALESCE(listnew.User, '')
     or COALESCE(metroList.Email, '')   <> COALESCE(listnew.Email, '')
     or COALESCE(metroList.LocName, '') <> COALESCE(listnew.LocName, '')
   ) THEN

Of course, this assumes that you're fine with NULL meaning the same as an empty string (which may not be appropriate).

Take a look at this BOL article on NULL comparisons.