Batch update in T-SQL

Question

I am using following script to delete entries from database in small batches:

SET @r = 1;
WHILE @r > 0
BEGIN 
  DELETE TOP (100000)
    [doc].[Document]
    WHERE Id IN (SELECT d.Id FROM @documentIds d); 
  SET @r = @@ROWCOUNT;
END

how can I update the table in same manner? I do not have LIMIT and OFFSET in T-SQL. I am also considering performance aspects.

http://stackoverflow.com/q/1198364/1080354 – gotqn Jun 30 '16 at 11:22 — gotqn, Jun 30 '16 at 11:22

score 2 · Accepted Answer · answered Jun 30 '16 at 11:03

You can update from the temporary table and then delete (or invalidate) rows in that table. Something like this:

SET @r = 1;
WHILE @r > 0
BEGIN 
    UPDATE d
        SET col = dd.col
        FROM doc.Document d JOIN
             (SELECT TOP 10000 FROM @documents dd ORDER BY id) dd
             ON d.id = dd.id;

    DELETE d TOP 10000 FROM (SELECT TOP 10000 @documents ORDER BY id) d;

    SET @r = @@ROWCOUNT;
END;

Jatin Patel · Answer 2 · 2016-06-30T14:07:47.907

Try this, use start and end values and increment it in batches

DECLARE @Batch  INT
        ,@StartId BIGINT
        ,@EndId BIGINT
        ,@r     INT

SELECT  @Batch  = 10000
        ,@StartId = 1
        ,@EndId = 0
        ,@r     = 1

WHILE @r > 0
BEGIN 
    SET @StartId = @EndId + 1
    SET @EndId = @EndId + @Batch

    UPDATE d
        SET col = dd.col
        FROM doc.Document d 
        INNER JOIN @documents dd ON d.id = dd.id
            AND dd.id BETWEEN @StartId AND @EndId

    SET @r = @@ROWCOUNT

END

above approach work only when you have ids in sequence, otherwise use this approach to generate batches beforehand and use it, this will make sure that each time 10000 record get updated.

DECLARE @Batch  INT
        ,@StartId BIGINT
        ,@EndId BIGINT
        ,@Cnt INT
        ,@TotalIds INT

DECLARE @Docs TABLE 
(
        StartId BIGINT, 
        EndId BIGINT, 
        BatchID INT
)

SELECT  @Batch  = 10000
        ,@StartId = 1
        ,@EndId = 0
        ,@Cnt = 1
        ,@TotalIds = 0

;WITH CTE_Docs AS
(   SELECT TOP (100) PERCENT id, ROW_NUMBER() OVER (ORDER BY id) as RowID   -- Give seq numbers to each row
    FROM @documentIds d 
)       
-- create batches and batch start and end point
INSERT INTO @Docs(StartId, EndId, BatchId ) 
SELECT  MIN(id) StartID, 
        MAX(id) EndID, 
        (RowID/@Batch)+1 AS BatchID             
FROM CTE_Docs 
GROUP BY RowID/@Batch
ORDER BY BatchID

-- get counter to loop through
SELECT @TotalIds = MAX(BatchID)
FROM @Docs

WHILE @Cnt <= @TotalIds BEGIN   

    SELECT  @StartID    = StartID,
            @EndID      = EndID
    FROM @Docs
    WHERE BatchID = @Cnt

    UPDATE d
        SET col = dd.col
        FROM doc.Document d 
        INNER JOIN @documents dd ON d.id = dd.id
            AND dd.id BETWEEN @StartId AND @EndId

    SET @Cnt = @Cnt + 1

END

Hope this helps.

Ids are not sequental. They could have spaces between – Alex Zhukovskiy Jun 30 '16 at 13:35 — Alex Zhukovskiy, Jun 30 '16 at 13:35
I have added another approach for non-sequential Ids – Jatin Patel Jun 30 '16 at 13:39 — Jatin Patel, Jun 30 '16 at 13:39

score -1 · Answer 3 · answered Jun 30 '16 at 11:15

Do this. This will delete 1000 records every 300 milliseconds. But the good thing what I do here, is that I release the transaction, and allow another transaction to complete. Because there could be another CRUD statement. This query will not block transactions, I use this to DELETE/UPDATE millions of records on a production server. Because the answers I see, will still block other transactions, because the process will still be attached to a transaction which will cause high CPU and DISK IO. Also I set the DEADLOCK PRIORITY to low so another transaction has higher importance. It does take longer. But this is SAFER for Server costs and no blocked transactions.

SET DEADLOCK_PRIORITY -10

DECLARE @r = 1;

WHILE @r > 0 > 0
BEGIN 
    DELETE TOP (1000)
    [doc].[Document]
    WHERE Id IN (SELECT d.Id FROM @documentIds d); 
    SET @r = @@ROWCOUNT;

    WAITFOR DELAY '00:00:00.300'

END

Batch update in T-SQL

3 Answers3