# Sql-server – SQL Server – MERGE join condition with NULLable Columns

mergesql-server-2012

I am trying to merge two of my tables (identical definition).

MERGE INTO xx.dbo.acc_s AS Target
USING yy.dbo.acc_s AS Source
ON (Target.acc_id= Source.acc_id AND Target.s_id= Source.s_id AND a_code= Source.a_code)
WHEN NOT matched BY Target THEN
INSERT (acc_id,s_id,a_code)
VALUES (Source.acc_id,Source.s_id,Source.a_code);


What I expected is that "If Target doesn't have the row, INSERT it" – Nothing Else.

I got a "Violation of PRIMARY KEY" error on one of the rows. If it's a matching row BY TARGET, it shouldn't have attempted to insert it in the first place.

What I did after that was:

MERGE INTO xx.dbo.acc_s AS Target
USING yy.dbo.acc_s AS Source
ON (Target.acc_id= Source.acc_id AND Target.s_id= Source.s_id AND ISNULL(a_code, '')= ISNULL(Source.a_code, '')
WHEN NOT matched BY Target THEN
INSERT (acc_id,s_id,a_code)
VALUES (Source.acc_id,Source.s_id,Source.a_code);


And it picked up correctly. From this, I am thinking that when I am merging, if I have two NULL columns joining – SQL Server cannot resolve this correctly. Have I encountered an expected behaviour here?

I know that I should probably rather use

WHEN matched THEN
UPDATE SET a_code = Source.a_code


But in that case I might have to change quite a lot of things as I am trying to find a general way to update other tables by creating a template query. However, I understand that it might not be practical.

Mark Sinkinson nailed it in the comments:

This is standard behaviour. NULL does not equal NULL

WHERE Destination.ColumnA = Source.ColumnA
AND ...  -- other column comparisons


to the following:

WHERE ((Destination.ColumnA = Source.ColumnA)
OR (Destination.ColumnA IS NULL AND Source.ColumnA IS NULL))
AND ...  -- other column comparisons


This will provide a workaround that effectively equates a NULL in one column to a NULL in another.

So to work that into your Merge, it would be as follows:

MERGE INTO xx.dbo.acc_s AS Target
USING yy.dbo.acc_s AS Source
ON (Target.acc_id= Source.acc_id
AND Target.s_id= Source.s_id
AND (a_code= Source.a_code
OR (a_code IS NULL AND Source.a_code IS NULL)
)
)
WHEN NOT matched BY Target THEN
INSERT (acc_id,s_id,a_code)
VALUES (Source.acc_id,Source.s_id,Source.a_code);