CODEWITHSUNDEEP
sql-serverdatabaset-sqlarchive
Chen_Dogg
Chen_Dogg

Reputation: 91

How do I setup a daily archive job in SQL server to keep my DB small and quick?

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.

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.

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. ").

Below is my current 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] < @7daysago)
BEGIN 
    BEGIN TRAN 

    INSERT INTO @NextIDs(IndexID)
        SELECT TOP 10000 IndexID FROM [dbo].[Data Import] WHERE [Data Import].[Receive Date] < @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

What am I doing wrong here? Im using SQL server 2012 Express, so cannot partition (which would be ideal).

Beyond this, how do I turn this into a daily recurring task? Any help would be much appreciated.

Upvotes: 0

Views: 2770

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294407

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

So... set identity insert on. Also, use DELETE ... OUTPUT INTO ... rather than SELECT -> INSERT -> DELETE.

DECLARE @7daysago datetime
SELECT @7daysago = DATEADD(d, -7, GetDate());
SET IDENTITY_INSERT [dbo].[Archived Data Import] ON;

WITH CTE as (
    SELECT TOP 10000 *
    FROM [dbo].[Data Import] 
    WHERE [Data Import].[Receive Date] < @7daysago)
DELETE CTE
  OUTPUT DELETED.id, DELTED.col1, DELETED.col2, ... 
  INTO  [dbo].[Archived Data Import] (id, col1, col2, ....);

Beyond this, how do I turn this into a daily recurring task?

Use conversation timers and activated procedures. See Scheduling Jobs in SQL Server Express.

Upvotes: 1

DB101
DB101

Reputation: 633

Without seeing your Table definitions, I am going to assume that your archive table has the same definition as your current table. Am I right in assuming that You have an identity column as Archived Data Import.IndexID? If so, switch it to ba an int large enough to hold expected values.

In order to schedule, this you will need to create a bat file to run this procedure and schedule it with windows scheduler.

Upvotes: 0

Related Questions