title: How do I setup a daily archive job in SQL server to keep my DB small and quick?
body: <p>I have a DB in SQL server and one of the tables recieves a large amount of data every day (+100 000). The data is reported on, but my client only needs it for 7 days. On the odd occasion he will require access to historic data, but this can be ignored.</p>
  
  <p>I need to ensure that my primary lookup table stays as small as can be (so that my queries are as quick as possible), and any data older than 7 days goes into a secondary (archiving) table, within the same database. Data feeds in consistently to my primary table throughout the day from a variety of data sources. </p>
  
  <p>How would I go about performing this? I managed to get to the code below through using other questions, butI am now recieving an error ("Msg 8101, Level 16, State 1, Line 12
  An explicit value for the identity column in table 'dbo.Archived Data Import' can only be specified when a column list is used and IDENTITY_INSERT is ON. ").</p>
  
  <p>Below is my current code:</p>
  
  <pre><code>    DECLARE @NextIDs TABLE(IndexID int primary key)
  DECLARE @7daysago datetime
  SELECT @7daysago = DATEADD(d, -7, GetDate())
  
  WHILE EXISTS(SELECT 1 FROM [dbo].[Data Import] WHERE [Data Import].[Receive Date] &lt; @7daysago)
  BEGIN 
      BEGIN TRAN 
  
      INSERT INTO @NextIDs(IndexID)
          SELECT TOP 10000 IndexID FROM [dbo].[Data Import] WHERE [Data Import].[Receive Date] &lt; @7daysago
  
      INSERT INTO [dbo].[Archived Data Import]
          SELECT * 
          FROM  [dbo].[Data Import] AS a
          INNER JOIN @NextIDs AS b ON a.IndexID = b.IndexID
  
      DELETE [dbo].[Data Import]
      FROM  [dbo].[Data Import] 
      INNER JOIN @NextIDs AS b ON a.IndexID = b.IndexID 
  
      DELETE FROM @NextIDs
  
      COMMIT TRAN
  END
  </code></pre>
  
  <p>What am I doing wrong here? Im using SQL server 2012 Express, so cannot partition (which would be ideal). </p>
  
  <p>Beyond this, how do I turn this into a daily recurring task? Any help would be much appreciated.</p>