title: COMMIT Transaction based on SQL SELECT every N rows
body: <p>Could possibly do this with a CURSOR..  However, we are writing out a file for a rollback on a go-live in a couple weeks.</p>
  
  <p><strong>ANSWER/UPDATE</strong>: Wound up using a CURSOR.  Probably not the best way to do this, but works.</p>
  
  <pre><code>DECLARE     @updateCount bigint 
  DECLARE     @AnId BIGINT
  DECLARE     @aField NVARCHAR(1000)
  DECLARE     @NxtID BIGINT
  
      -- Initialize the update count
      set @updateCount = 0
  
      DECLARE updatecursor CURSOR FOR
          SELECT id,
           field,
           ORG_ID
              FROM some_table
              WHERE Another_ID IN(SELECT Another_ID FROM Another_Table WHERE NAME='Business_Group') 
      OPEN updatecursor
      FETCH NEXT FROM updatecursor INTO @AnId, @aField,@NxtID
      WHILE @@fetch_status = 0
      BEGIN
          -- Begin transaction for the first record
          if @updateCount = 0
          BEGIN 
              PRINT 'BEGIN TRANSACTION'
              BEGIN TRANSACTION
          END
  
          PRINT 'UPDATE some_table SET Field='+Char(39)+@aField+Char(39)+' WHERE ID='+CONVERT(VARCHAR,@AnId)+' and Another_ID='+CONVERT(NVARCHAR,@NxtID)+''
  
          set @updateCount = @updateCount + 1
          -- Commit every 1000 records and start a new transaction
          IF @updateCount % 1000 = 0 
          BEGIN
              PRINT 'COMMIT TRANSACTION'
              PRINT 'WAITFOR DELAY ''00:00:01'''
              -- DONE WITH THE PREVIOUS GROUP, WE NEED THE NEXT
              PRINT 'BEGIN TRANSACTION'
              COMMIT TRANSACTION
              BEGIN TRANSACTION
          END
          FETCH NEXT FROM updatecursor INTO @AnId,@aField,@NxtID
      END
      IF @updateCount % 1000 &lt;&gt; 0
      BEGIN
          -- COMMIT FINAL WHEN TO THE END
          PRINT 'COMMIT TRANSACTION'
          PRINT 'WAITFOR DELAY ''00:00:01'''
          COMMIT TRANSACTION
      END
      CLOSE updatecursor
      DEALLOCATE updatecursor
  go
  </code></pre>
  
  <p>What we would like to do is to write in the rollback SQL Script every 1000 or 10000 rows a COMMIT..  The select works fine and creates the UPDATE ok as well.</p>
  
  <p>However, we have about 200k records to UPDATE.  This is a rollback script, however when testing the rollback this morning SQL Server did not like the huge amount of records.</p>
  
  <p>Have seen this: <a href="http://www.sqlusa.com/bestpractices2005/hugeupdate/" rel="nofollow">http://www.sqlusa.com/bestpractices2005/hugeupdate/</a> and would be great if all of the records were the same, but they are not.</p>
  
  <p>Because of our limited permissions set Corporate does not allow the use of OPENROWSET or related functions..</p>
  
  <p>Here is what we have so far:</p>
  
  <pre><code>-- 202,739
  DECLARE @UPDATECOUNT INT
  SET @UPDATECOUNT=0
  
  SELECT 
      'UPDATE some_table SET field='+original_field+' WHERE ID='+ID+'' 
   as 'BackupData'
  FROM some_table (nolock)  
  WHERE Another_ID IN(SELECT Another_ID FROM Another_Table WHERE NAME='Business-group') 
  
          set @UPDATECOUNT = @UPDATECOUNT + 1
          -- Commit every 10000 records and start a new transaction
          IF @UPDATECOUNT % 10000 = 0 
          BEGIN
              COMMIT TRANSACTION
              BEGIN TRANSACTION
          END
  </code></pre>
  
  <p>Thanks!</p>