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.