1

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.

0

1 Answer 1

1

Mark Sinkinson nailed it in the comments:

This is standard behaviour. NULL does not equal NULL

What you can do is adjust your predicate from this:

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);
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.