Wednesday, 12 March 2014

TSQL - Nibble Update

I've often had to utilize a 'Nibble Delete' approach to removing a large volume of data. Doing so prevents record locking and I/O requests overwhelming a system. I had to do the same with an UPDATE statement today, to combine a couple of columns. A generic version of the script is below.

-- Nibble update

DECLARE @RowsToUpdate int
SELECT @RowsToUpdate = COUNT(*) FROM TableToUpdate WHERE myColumn = ''

DECLARE @Progress int
SET @Progress = 0

WHILE @Progress < @RowsToUpdate

 UPDATE TOP (1000) TableToUpdate
 SET myColumn = cast(rtrim(column1) as varchar(150)) + ' \\ ' + cast(rtrim(column2) as varchar(150))
 FROM TableToUpdate
 WHERE myColumn = ''

    SET @Progress = @Progress + @@RowCount

No comments: