Friday, January 2, 2015

Batch Update : using TOP n

---do not use set rowcount.
--Use top n
WHILE (1=1)
  BEGIN
     BEGIN TRANSACTION
    UPDATE r
    SET col1= col2
    from Table1 r
    where r.col3 in (select top 10000 col3 from Table1 where col1 is null)

   -- Update 10000 nonupdated rows

   IF @@ROWCOUNT = 0
     BEGIN
       COMMIT TRANSACTION
       BREAK
    END

    COMMIT TRANSACTION
END

No comments:

Post a Comment