MS SQL Server Merge statement does not update NULL values

Question

I have two tables, one in database db1 and another one in db2. They both are identical and only have id, name (varchar) and data (float).

I do this query to move the data update source table in db1 to target db2:

   MERGE [db1].[dbo].fake_table as target USING (
          select
                 src.id,
                 src.data,src.name
          from [db2].[dbo].fake_table
   src  where 1=1 ) as source (
          id,
          data,name
   )
   on (
          target.id=source.id
   )
   WHEN MATCHED AND
          (target.data<>source.data OR target.name<>source.name)
          THEN
                 UPDATE SET target.data=source.data,target.name=source.name
   WHEN NOT MATCHED BY target
          THEN 
                 INSERT (
                        id,
                        data,name
                 )
                 Values (id,
                        data,name
                 )
   WHEN NOT MATCHED BY source
          THEN DELETE;

It works for all kind of rows but not the ones that have NULL as a value

Answer 1

See the WARNING from J.D. about the bugs in MERGE

However the problem you are facing with the NULL value is because you exclude them in your comparison:

WHEN MATCHED AND (
                    target.data <> source.data
                    OR target.name <> source.name
                 )

This can be changed to statement below (or perhaps you can just remove the check):

MERGE [db1].[dbo].fake_table AS target
USING (
         SELECT
               src.id,
               src.data,
               src.name
         FROM  [db2].[dbo].fake_table src
         WHERE 1 = 1
      ) AS source (id, data, name)
ON (target.id = source.id)
WHEN MATCHED AND (
                    target.data <> source.data
                    OR target.name <> source.name

                    OR (target.data IS NULL AND source.data IS NOT NULL)
                    OR (target.data IS NOT NULL AND source.data IS NULL)

                    OR (target.name IS NULL AND source.name IS NOT NULL)
                    OR (target.name IS NOT NULL AND source.name IS NULL)

                 ) THEN UPDATE SET
                           target.data = source.data,
                           target.name = source.name
WHEN NOT MATCHED BY TARGET THEN INSERT (
                                          id,
                                          data,
                                          name
                                       )
                                VALUES
                                (
                                   id,
                                   data,
                                   name
                                )
WHEN NOT MATCHED BY SOURCE THEN DELETE;

Also don't forget when comparing FLOAT values can give problems as Floating point values are only approximate values rather than exact values. If you need to make an equals comparison, you need to use decimal values.

Oh, and next time please include some scripts to generate the schema and some testdata.