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;