title: Move TOP (x) records from one table to another with WHERE conditions
body: <p>I need to move the <strong>TOP (X)</strong> oldest rows from a live table into an ARCHIVE table, but the rows are not in date order.</p>
  
  <p>Data will be moved on a DATE criteria and due to the fact that there are millions of records that may need to be moved over time, this will be done gradually.  Therefore the queries will use a <strong>SELECT TOP</strong> and <strong>DELETE TOP</strong>. </p>
  
  <p>My SQL Script so far is</p>
  
  <pre><code>INSERT INTO tblDataLoggedArchived 
  SELECT TOP(1000) *
  FROM tblDataLogged
  WHERE fldDateTime &lt;= @ExpiryDate 
  ORDER BY fldDateTime ASC
  
  DELETE TOP(1000) FROM tblDataLogged
  WHERE fldDateTime &lt;= @ExpiryDate
  ***** ORDER BY fldDateTime ASC  **** HERE's the problem
  COMMIT 
  </code></pre>
  
  <p>However - here's the problem.</p>
  
  <p>The records that are copied into the archive table have an ORDER BY clause applied to the SELECT. I need to make sure that these records are deleted from the original table - but you cannot specify an ORDER BY for the DELETE TOP query</p>
  
  <p>I am by no means an SQL expert. Can someone please advise me the best way of moving the TOP X records from one table to another, using an ORDER BY parameter?</p>
  
  <p>Note, the original table does not have any indexes.</p>
  
  <p>Thanks</p>