We have the following update script we are trying to do in batches.
Currently the execution never stops running - so needed help understanding what's wrong with the logic or syntax that's missing.
--Create Temp table
CREATE TABLE #TempIFSC ([EnrolledPaymentMethodAccountId] uniqueidentifier,
[PaymentAccountId] uniqueidentifier,
[ExternalSystemId] int,
[EnrolledPaymentMethodAccountStatusId] int,
[Extension] xml,
[EnrollmentAccountRevisionId] int,
[BankName] nvarchar(100) NULL,
[BankBranch] nvarchar(100) NULL,
[IFSC] nvarchar(100) NULL);
--Insert record into temp table who does not have IFSC code and currency INR
INSERT INTO #TempIFSC
SELECT EP.[EnrolledPaymentMethodAccountId],
EP.[PaymentAccountId],
EP.[ExternalSystemId],
EP.[EnrolledPaymentMethodAccountStatusId],
CAST(EP.[Extension] AS xml),
EP.[EnrollmentAccountRevisionId],
NULL,
NULL,
NULL
FROM [dbo].[EnrolledPaymentMethodAccount] EP WITH (NOLOCK)
INNER JOIN [dbo].[PaymentAccount] PA WITH (NOLOCK) ON EP.PaymentAccountId = PA.PaymentAccountId
AND PA.CurrencyCode = 'INR'
WHERE ISNULL(CAST(EP.Extension AS xml).value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'), '') = ''
AND EP.ExternalSystemId = 52
AND EP.EnrolledPaymentMethodAccountStatusId = 1;
--Update the BankName and BankBranch value in temp table
DECLARE @Rowcount int = 1;
WHILE (@Rowcount > 0)
BEGIN
UPDATE TOP (4999)
#TempIFSC
SET BankName = b.Name,
BankBranch = TMP.Extension.value('(//*[local-name()="Value"])[3]', 'NVARCHAR(255)')
FROM #TempIFSC TMP
INNER JOIN [dbo].[Bank] b ON b.ExternalSystembankId = TMP.Extension.value('(//*[local-name()="Value"])[2]', 'NVARCHAR(255)')
WHERE b.ExternalSystemId = TMP.ExternalSystemId
AND b.CurrencyCode = 'INR';
SET @Rowcount = @@ROWCOUNT;
PRINT @Rowcount;
CHECKPOINT;
END;
--Update IFSC value in temp table
DECLARE @IFSCRowcount int = 1;
WHILE (@IFSCRowcount > 0)
BEGIN
UPDATE TOP (4999)
#TempIFSC
SET IFSC = IM.IFSC
FROM #TempIFSC TMP
INNER JOIN [taurus].[IFSCMasterList] IM (NOLOCK) ON TMP.BankBranch = IM.BankBranchName
AND TMP.BankName = IM.BankName;
SET @IFSCRowcount = @@ROWCOUNT;
CHECKPOINT; --<-- to commit the changes with each batch
END;
--Remove blank node of IFSC
DECLARE @XMLRowcount int = 1;
WHILE (@XMLRowcount > 0)
BEGIN
DECLARE @NodeName nvarchar(500) = N'NameValueEntity';
UPDATE TOP (4999)
#TempIFSC
SET Extension.modify('delete /ArrayOfNameValueEntity/*[local-name(.) eq sql:variable("@NodeName")][6]')
FROM #TempIFSC
WHERE Extension.exist(N'/*/NameValueEntity/Name[text()="IFSCCode"]') = 1
AND ISNULL(Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'), '') = '';
SET @XMLRowcount = @@ROWCOUNT;
PRINT @XMLRowcount;
CHECKPOINT; --<-- to commit the changes with each batch
END;
--Update extension value in temp table
DECLARE @CodeRowcount int = 1;
WHILE (@CodeRowcount > 0)
BEGIN
UPDATE TOP (4999)
#TempIFSC
SET Extension.modify('insert <NameValueEntity><Name>IFSCCode</Name><Value>{sql:column("#TempIFSC.IFSC")}</Value></NameValueEntity>
into (/ArrayOfNameValueEntity)[1]')
FROM #TempIFSC
WHERE ISNULL(IFSC, '') <> '';
SET @CodeRowcount = @@ROWCOUNT;
CHECKPOINT; --<-- to commit the changes with each batch
END;