Merge not updating in explicit transaction

Question

Merge not updating in explicit transaction

Old Hand

Points: 340

May 28, 2020 at 6:09 pm

We have the following code in a stored procedure which has a merge statement and an update statement to a different table in an explicit transaction. The merge update does not update any data in the inventory.gtin table but the update statement below it works and data is updated in the import.file_data table when the stored procedure is called in a batch job. But, when the stored procedure is executed manually, the merge works fine and updates/inserts data. Is there anything missing in the code that may be causing this?

DECLARE @targetVendorCode nvarchar(20)
DECLARE gtin_cursor CURSOR FOR  

SELECT DISTINCT aVendorCode
FROM #fileData fd


OPEN gtin_cursor   
FETCH NEXT FROM gtin_cursor INTO @targetVendorCode
WHILE @@FETCH_STATUS = 0   
BEGIN   

BEGIN TRANSACTION;

MERGE [inventory].[gtin] AS target  
USING (SELECT * FROM #fileData WHERE ngtin IS NOT NULL AND aVendorCode = @targetVendorCode ) AS source
ON (target.[ngtin] = source.[ngtin])
WHEN MATCHED AND ( ( source.createDateTime > COALESCE(target.fileCreateDateTime,'04/02/1982') ) OR source.[status] = 'override' ) THEN
UPDATE SET 
[skuId] = source.[skuId],  
[lastUpdateFileId] = source.[fileId],  
[vendorCode] = COALESCE(source.[aVendorCode],source.[vendorCode]),  
[lastUpdateDateTime] = source.[lastUpdateDateTime],
[fileCreateDateTime]= source.createDateTime
WHEN NOT MATCHED BY TARGET THEN 
INSERT (
[skuId],
[lastUpdateFileId],
[vendorCode],
[lastUpdateDateTime],
[fileCreateDateTime]
)  
VALUES (
source.[skuId],
source.[fileId],
COALESCE(source.[aVendorCode],source.[vendorCode]),
source.[lastUpdateDateTime],
source.createDateTime
) 

SELECT @action = '[{"type":"distribute","actionDateTime":"' + CONVERT(VARCHAR(19), GETUTCDATE(), 120) + '","insertDateTime":"' + CONVERT(VARCHAR(19), GETUTCDATE(), 120) + '","referenceId":' + TRY_CAST(@fileId AS NVARCHAR(20)) + '}]'

UPDATE fd SET [status] = 'processed',[modifiedDateTime] = GETUTCDATE(),[modifiedUserName] = SYSTEM_USER FROM [import].[file_data] fd WHERE fileId = @fileId


IF @@TRANCOUNT > 0
BEGIN
COMMIT TRANSACTION;
END
ELSE
BEGIN
ROLLBACK TRANSACTION;
END

FETCH NEXT FROM gtin_cursor INTO @targetVendorCode
END   

CLOSE gtin_cursor   
DEALLOCATE gtin_cursor

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11393 More actions · Answer 1

Does the merge insert? The update has additional criteria it is checking. Also, Why are you using a cursor, this looks like a set based operation would work faster? I'd also be worried about the cursor. You are selecting distinct which indicates there could be multiple avendorcode, but then you select from the same temp table by avendorcode, so that could be finding multiple records. Also, a try/catch block would help to find the issues.

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sadie Old Hand Points: 340 More actions · Answer 2

Thank you for the response. The code actually has a try/catch logic wrapped in it which I omitted. There weren't any errors in the batch job when executed. Also, the conditions in the WHEN MATCHED are always true and the cursor is needed to process for each distinct vendor. In regards to selecting from the same temp table by avendorcode, there could definitely be multiple records for that same avendorcode and those records that match the join should be updated. I have read warnings about using merge because there are bugs in it that have not been fixed.

Steve Collins SSCrazy Points: 2068 More actions · Answer 3

As separate statements maybe something like this

set xact_abort on;

begin transaction;
begin try
    update g
    set
      [skuId] = source.[skuId],  
  [lastUpdateFileId] = source.[fileId],  
  [vendorCode] = coalesce(source.[aVendorCode],source.[vendorCode]),  
  [lastUpdateDateTime] = source.[lastUpdateDateTime],
  [fileCreateDateTime]= source.createDateTime
    from
      [inventory].[gtin] g
     join
      #filedata f on f.aVendorCode=g.vendorCode
                     and f.[ngtin] = g.[ngtin]
                     and (f.createDateTime > coalesce(g.fileCreateDateTime,'04/02/1982') 
                          or f.[status] = 'override');

    insert [inventory].[gtin]([skuId], [lastUpdateFileId], [vendorCode], [lastUpdateDateTime], [fileCreateDateTime])
    select 
      [skuId], [fileId], coalesce([aVendorCode],[vendorCode]), [lastUpdateDateTime], createDateTime
    from
      #filedata f
    where not exists(select 1
                     from [inventory].[gtin] g 
                     where f.aVendorCode=g.vendorCode
                           and f.[ngtin] = g.[ngtin]);
    commit transaction;
end try
begin catch
    print ('did not work');

    rollback transaction;
end catch

This reply was modified 3 years, 11 months ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können