In my case, the LGI_GROUPID is a primary key with identity increment and therefore the code you supplied may not work. I modified my code as follows and am still not getting any inserts. In the statement below, LG_LOANID of value 22711 does not exist in the source or target table. I think I am doing this incorrect as for the MERGE to work, you need to have a record in Source or Target. I think what I need to use is IF EXISTS....ELSE.
Modified code is;
SET IDENTITY_INSERT LOAN_GROUP_INFO ON DECLARE @NEXT_ID AS INT = (SELECT IDENT_CURRENT('LOAN_GROUP_INFO') + 1) MERGE INTO LOAN_GROUP_INFO WITH (HOLDLOCK) AS TARGET USING (SELECT * FROM LOAN_GROUPING WHERE LG_LOANID = 22711 AND LG_ISACTIVE = 1) AS SOURCE ON TARGET.LGI_GROUPID = SOURCE.LGI_GROUPID WHEN MATCHED THEN UPDATE SET LGI_GROUPLVR = 12 WHEN NOT MATCHED BY TARGET THEN INSERT (LGI_GROUPID,LGI_GROUPLVR,LGI_GROUPEXPOSURE,LGI_DATECREATED,LGI_CREATEDBY,LGI_DATEUPDATED,LGI_UPDATEDBY,LGI_ISACTIVE) VALUES(@NEXT_ID,NULL,NULL,CURRENT_TIMESTAMP,3,NULL,NULL,1); SET IDENTITY_INSERT LOAN_GROUP_INFO OFF