Replacing Merge in SQL Server with some other code to have the same functionality

68bkxrlz 于 2023-08-02 发布在 SQL Server

关注(0)|答案(1)|浏览(113)

How can I replace the merge query with some other code in SQL Server 2012 in below code to get the same functionality.

MERGE ReprocessPRESs with (ROWLOCK) AS TARGET   
 USING   
 (      
 SELECT @PRId AS PRId,      
   Val AS ESId,  
   GETDATE() as CreateDate      
 FROM @ESIds      
 ) AS SOURCE (PRId,ESId,CreateDate)      
 ON TARGET.PRId= SOURCE.PRId AND TARGET.ESId = SOURCE.ESId
 WHEN NOT MATCHED BY TARGET       
  AND SOURCE.PRId = @PRId 
   THEN INSERT (PRId,ESId,CreateDate) 
  VALUES (PRId,ESId,CreateDate)      
 WHEN NOT MATCHED BY SOURCE       
  AND TARGET.PRId = @PRId
   THEN DELETE;

Where @ESIds is a User defined type of IntList

Basically it should update, insert or delete accordingly.

sql-server

来源：https://stackoverflow.com/questions/76661192/replacing-merge-in-sql-server-with-some-other-code-to-have-the-same-functionalit

1条答案

按热度按时间

fnatzsnv1#

The exact same code as separate statements would be:

BEGIN TRAN;

INSERT ReprocessPRESs WITH (ROWLOCK)
SELECT
  @PRId AS PRId,      
  SOURCE.Val AS ESId,  
  GETDATE() as CreateDate      
FROM @ESIds SOURCE
WHERE NOT EXISTS (SELECT 1
    FROM ReprocessPRESs TARGET  -- you should add UPDLOCK, HOLDLOCK here
    WHERE TARGET.PRId = @PRId
      AND TARGET.ESId = SOURCE.ESId
);
     
DELETE TARGET
FROM ReprocessPRESs TARGET WITH (ROWLOCK)
WHERE TARGET.PRId = @PRId
  AND NOT EXISTS (SELECT 1
    FROM @ESIds SOURCE
    WHERE TARGET.ESId = SOURCE.Val
);

COMMIT;

Having said that, you would be advised to use UPDLOCK, HOLDLOCK hints on the inner side of the EXISTS in the INSERT section.

赞(0）回复(0）举报 2023-08-02

我来回答

Replacing Merge in SQL Server with some other code to have the same functionality

1条答案

相关问题

热门标签

最新问答