SSMS Looping When Updating a Table in Batches and Summing 2 Variables

Other SQL Server Topics
1

Hi guys,

Brain teaser for you..

I am trying to update a large table in small batches. Within the update statement, I am setting a target field equal to its previous value plus a value in the source table. This works fine in a single query, but if I split the query into smaller batches, things go into an infinite spin. I have produced some sample code below which illustrates the issue:

-- Create test tables and insert values
drop table if exists a
drop table if exists b
create table a (var1 nvarchar(10),var2 nvarchar(10),var3 int,amount int)
create table b (var1 nvarchar(10),var2 nvarchar(10),var3 int,amount int)

insert into a
values ('abcd','efgh',4,1),('abcd','ghij',2,8)

insert into b
values ('abcd','ghij',1,16),('abcd','efgh',8,32)

-- Create table for update output
drop table if EXISTS #updateOutput
create table dbo.#updateOutput ([table] nVarChar(32)
,action nvarchar(10)
,id nVarChar(50)
,id2 nvarchar(50)
,id3 nvarchar(50)
,[updates] nVarChar(500))

-- Perform update on 2 records in batches of 1
declare @rows INT, @count INT;

SET @rows = 1; -- Number of Records to Load Per Batch
SET @count = 0;

WHILE @rows > 0
BEGIN
BEGIN TRAN

update top (@rows) a
set amount = source.amount + target.amount

-- Write history table of inserts and any updated attributes that changed
OUTPUT 'CLAIMS'
,'UPDATE'
,SOURCE.var1
,source.var2
,source.var3
,case
when cast(inserted.amount as int) <> cast(deleted.amount as int) then
cast(concat('AMOUNT Old: ',deleted.amount,' New: ',inserted.amount,' ') as nVarChar(max))
else ''
end
--into #updateOutput

from a target INNER JOIN
b source
on source.var1 = target.var1
and source.var2 = target.var2
and source.amount <> target.amount

-- Increment batch counters
SET @rows = @@ROWCOUNT
SET @count = @count + @rows

COMMIT TRAN
END

If someone has a solution to this, I would be super grateful :smile:

Cheers,
Simon

2

Not many details to go on, but maybe a flag in the table to indicate that it has already been sum'med and ignore that row the next time?

1 Like
3

Thanks Scott, appreciate the reply.
Are you able to give me an example of your suggestion?

The above code is generic and can be run to reproduce the issue. The issue only occurs when running in small batches and setting of @@rowcount appears to be affected by the summing of the source and target fields. When i avoid the sum and set the amount field to either the target or the source it works fine but the issue occurs specifically when introducing the sum with the batch logic driven by @@rowcount.

Hope this helps narrow it down a little more.

Cheers
Simon

4

Hi guys,

All makes sense now.
All variables except the sum are being compared within the ON clause but the sum will never match so it goes into a spin.
As Scott said, I need some kind of flag so each record only gets updated no more than once.

Thanks again,

Simon