SQL Server – Why MERGE Performs Additional INSERTS

mergesql serversql-server-2016

I am facing the followign issue, my MERGE statements based on data (views) that do not deliver primary keys or any unique keys, always just ADD additional INSERTS on every execution to the target table.

Since I have used ALL COLUMNS in the comparison part, I was expecting that they are compared t the target and in case matched, NOT inserted but just left alone.

Instead the number of rows in the target keeps growing continuously with every execution.

Yes I know it might be better to deliver primary key. But I have not enough knowledge about the data delivered and in some cases it was not possible to find a primary key combination at all.

How can I prevent additional inserts in that case?

 MERGE INTO [Z].[IAPP] AS Target
    USING ( 
        SELECT 
    [SXD],[UUZQ],[SSRTD],[AAFFT],[IID] From [Z].[vIAPP] 
    ) AS Source ([SXD],[UUZQ],[SSRTD],[AAFFT],[IID])
    ON (Target.[SXD] = Source.[SXD] AND Target.[UUZQ] = Source.[UUZQ] AND Target.[SSRTD] = Source.[SSRTD] AND Target.[AAFFT] = Source.[AAFFT] AND Target.[IID] = Source.[IID])
    WHEN NOT MATCHED BY TARGET AND @Action = 'UI' THEN
        INSERT([SXD],[UUZQ],[SSRTD],[AAFFT],[IID])
        VALUES(Source.[SXD],Source.[UUZQ],Source.[SSRTD],Source.[AAFFT],Source.[IID])
    WHEN NOT MATCHED BY SOURCE AND @Action = 'D' THEN 
        DELETE;

Best Answer

I found the issue myself.

It was NULL values in the compared columns that could not be matched with one another. After adding ISNULL function around source and target to replace NULL in case it is there, it works as expected.

 MERGE INTO [Z].[IAPP] AS Target
    USING ( 
        SELECT 
    [SXD],[UUZQ],[SSRTD],[AAFFT],[IID] From [Z].[vIAPP] 
    ) AS Source 

([SXD],[UUZQ],[SSRTD],[AAFFT],[IID])

  ON (
     ISNULL(Target.[SXD],'nul') = Isnull(Source.[SXD],'nul') AND isnull(Target.[UUZQ],'nul') = isnull(Source.[UUZQ],'nul')  /* etc. */
    )
            WHEN NOT MATCHED BY 

TARGET AND @Action = 'UI' THEN
            INSERT([SXD],[UUZQ],[SSRTD],[AAFFT],[IID])
            VALUES(Source.[SXD],Source.[UUZQ],Source.[SSRTD],Source.[AAFFT],Source.[IID])
        WHEN NOT MATCHED BY SOURCE AND @Action = 'D' THEN 
            DELETE;
Related Question