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.