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 thats 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) = '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