title: Move SQL Server data in limited (1000 row) chunks
body: <p>I'm writing a process that archives rows from a SQL Server table based on a datetime column. I want to move all the rows with a date before X, but the problem is that there are millions of rows for each date, so doing a BEGIN TRANSACTION...INSERT...DELETE...COMMIT for each date takes too long, and locks up the database for other users.</p>
  
  <p>Is there a way that I can do it in smaller chunks? Maybe using ROWCOUNT or something like that?</p>
  
  <p>I'd originally considered something like this:</p>
  
  <pre><code>SET ROWCOUNT 1000
  
  DECLARE @RowsLeft DATETIME
  DECLARE @ArchiveDate DATETIME
  
  SET @ROWSLEFT = (SELECT TOP 1 dtcol FROM Events WHERE dtcol &lt;= @ArchiveDate)
  
  WHILE @ROWSLEFT IS NOT NULL
  BEGIN
  
      INSERT INTO EventsBackups
      SELECT top 1000 * FROM Events
  
      DELETE Events
  
      SET @ROWSLEFT = (SELECT TOP 1 dtcol FROM Events WHERE dtcol &lt;= @ArchiveDate)
  
  END
  </code></pre>
  
  <p>But then I realized that I can't guarantee that the rows I'm deleting are the ones I just backed up. Or can I...?</p>
  
  <p><strong>UPDATE:</strong>
  Another options I'd considered was adding a step:</p>
  
  <ol>
  <li>SELECT TOP 1000 rows that meet my date criteria into a temp table</li>
  <li>Begin Transaction</li>
  <li>Insert from temp table into archive table</li>
  <li>Delete from source table, joining to temp table across every column</li>
  <li>Commit transaction</li>
  <li>Repeat 1-5 until no rows remain that meet the date criteria</li>
  </ol>
  
  <p>Does anybody have an idea for how the expense of this series might compare to some of the other options discussed below?</p>
  
  <p><strong>DETAIL:</strong> I'm using SQL 2005, since somebody asked.</p>