2018-07-24 MERGE Insert not working
2018-07-24 MERGE Insert not working
Albert陈凯
发布于 2018-08-03 15:05:10
发布于 2018-08-03 15:05:10
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 本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2018.07.24 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读