SQLTeam.com | Weblogs | Forums

TSQL Batched update script just keeps running

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

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.

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```