TSQL Batched update script just keeps running

Transact-SQL
1

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 IFSCCode{sql:column("#TempIFSC.IFSC")}
into (/ArrayOfNameValueEntity)[1]')
FROM #TempIFSC
WHERE ISNULL(IFSC, '') <> ''

SET @CodeRowcount = @@ROWCOUNT;

CHECKPOINT; --<-- to commit the changes with each batch
END

2

The first loop looks like it will UPDATE the first 4999 over and over again, forever. There's no WHERE condition to redirect the UPDATE to different rows for each execution.

3

We have updated the script and mostly working now except for only one clause, looping constantly.
heres is the update code.


--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,
	[IsUpdate] bit 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,0
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)'),
		IFSC = IM.IFSC,
		IsUpdate = 1
	FROM #TempIFSC TMP
	INNER JOIN [dbo].[Bank] b WITH(NOLOCK) ON b.ExternalSystembankId = TMP.Extension.value('(//*[local-name()="Value"])[2]', 'NVARCHAR(255)') AND b.ExternalSystemId = TMP.ExternalSystemId 
	INNER JOIN [taurus].[IFSCMasterList] IM WITH(NOLOCK) ON b.Name = IM.BankName AND TMP.Extension.value('(//*[local-name()="Value"])[3]', 'NVARCHAR(255)') = IM.BankBranchName
	Where b.CurrencyCode = 'INR'
	AND b.Name IS NOT NULL
	AND ISNULL(TMP.Extension.value('(//*[local-name()="Value"])[3]', 'NVARCHAR(255)'),'') != ''
	
	SET @Rowcount = @@ROWCOUNT;
	
	print @Rowcount
	
	RETURN 
End

--Remove the records which does not have BankName or BankBranch
DELETE FROM #TempIFSC WHERE BankName IS NULL OR BankBranch IS NULL OR IFSC IS NULL

--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 WITH(NOLOCK) 
--		ON TMP.BankBranch = IM.BankBranchName and TMP.BankName = IM.BankName
--	WHERE IM.BankName IS NOT NULL 
--	AND IM.IFSC IS NOT NULL
--	AND IM.BankBranchName IS NOT NULL
--	--AND TMP.Extension.exist(N'/*/NameValueEntity/Name[text()="IFSCCode"]') = 1
--	--OR ISNULL(TMP.Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = ''

--	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,'') <> ''
	AND BankName IS NOT NULL
	AND IsUpdate = 1
	AND ISNULL(Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = ''

	SET @CodeRowcount = @@ROWCOUNT;

	CHECKPOINT;   --<-- to commit the changes with each batch
END

--Actual table updated from ##TempIFSC
Declare @TblRowcount INT = 1;

--WHILE (@TblRowcount > 0)   
--BEGIN
--	Update TOP (4999) [dbo].[EnrolledPaymentMethodAccount_20220921]
--	SET [Extension] = CAST(TMP.[Extension] AS nvarchar(max))
--	FROM [dbo].[EnrolledPaymentMethodAccount_20220921] EP WITH(NOLOCK)
--	INNER JOIN #TempIFSC TMP WITH(NOLOCK) ON EP.[PaymentAccountId] = TMP.PaymentAccountId AND EP.[EnrolledPaymentMethodAccountId] = TMP.[EnrolledPaymentMethodAccountId]
--	Where EP.[ExternalSystemId] = TMP.ExternalSystemId
--	AND EP.[EnrolledPaymentMethodAccountStatusId] = TMP.EnrolledPaymentMethodAccountStatusId
--	AND EP.[EnrollmentAccountRevisionId] = TMP.EnrollmentAccountRevisionId
--	AND TMP.IsUpdate = 1
--	AND TMP.IFSC <> '' OR TMP.IFSC IS NULL
--	AND TMP.BankName IS NOT NULL AND TMP.BankBranch IS NOT NULL
--	AND ISNULL(TMP.Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') != ''
	
--	SET @TblRowcount = @@ROWCOUNT;

--	CHECKPOINT;   --<-- to commit the changes with each batch
--END

Select * from #TempIFSC WITH(NOLOCK)

DROP Table #TempIFSC```
4

image (2)
image (2)1207×516 17.5 KB