https://www.sqlservercentral.com/Forums/1551626/MERGE-Insert-not-working
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
