First always create backup of table to be updated.

SELECT * INTO [dbo].[tblTable01_YYYYMMDD] FROM [dbo].[tblTable01] f WITH (NOLOCK) 

SELECT COUNT(*) FROM [dbo].[tblTable01] f WITH (NOLOCK) 
SELECT COUNT(*) FROM [dbo].[tblTable01_YYYYMMDD] f WITH (NOLOCK) 

Batch update records in a database table.

PRINT 'Update records from tblTable01'

CREATE TABLE #TempUpdateTable01
(
	Id BIGINT IDENTITY (1,1) PRIMARY KEY,
	TableId bigint
)

INSERT INTO #TempUpdateTable01
(
    TableId
)
SELECT
	f.TableId
FROM 
	[dbo].[tblTable01] f WITH (NOLOCK) 
WHERE 
	f.Column01 = 123

DECLARE @start BIGINT,
		@end BIGINT,
		@min BIGINT,
        @max BIGINT,
        @inc INT = 10000

SELECT @min = 0, @max = MAX(Id)
FROM #TempUpdateTable01

WHILE @min < @max 
BEGIN
                BEGIN TRANSACTION 

				IF (@min + @inc) >= @max 
                BEGIN
					SET @start = @min + 1
					SET @end = @max
				END           
                ELSE
				BEGIN
					SET @start = @min + 1
					SET @end = @min + @inc				
				END 
                               
					
				UPDATE
					f
				SET
					f.Column02 = 'new'
				FROM 
					[dbo].[tblTable01] f  
					INNER JOIN #TempUpdateTable01 t ON f.biTableId = t.TableId
				WHERE	 
					t.id BETWEEN @start AND @end
					
				PRINT 'Id: ' + CAST(@start AS VARCHAR(50)) + ' to ' + CAST(@end AS VARCHAR(50))	 

                COMMIT TRANSACTION;

                SELECT @min = @min + @inc;
END;

DROP TABLE #TempUpdateTable01
Last modified: January 8, 2020

Author

Comments

Write a Reply or Comment