locked
Archival Stored Procedure RRS feed

  • Question

  • User-718146471 posted

    Good morning everyone, I am trying to figure out how to move certain columns from one table to another. Here is the kicker; each column in this active table has a date format of the letter D with yyyymmdd (for example today would be D20161020). What I am trying to do is to take all columns from the previous month (for example D201609##) and move them to an archive table (copy then remove the copied columns). 

    My plan and thinking is to keep the current data lean to be as quick as possible. And yes I am using indexes, I just do not want the table to get that big. :) Each day, I import 3000+ records into that table. For slimness, I am referring by record ID to the master record table. Essentially, this table has a RecID Foreign Key to the master record table RecID and the date in that format Dyyyymmdd. I place the count for that date into the corresponding records.

    Here is the other thing, I am looking to only keep the current month's data in the active table.

    I plan to keep the archive tables by year, i.e 2016counts, 2017counts which will hold that year's data.

    I figure for the yearly archive table, I can simply create a proc that will copy all the data from the current archive table to the year archive and then truncate that active table. Or rename the table and just re-create a blank table like it since the active archive table will have nothing in it except for the first column which is a correlated record ID from a master table of records.

    It is not a question of whether I can figure this out, I am more looking for advice on how other developers would handle this unique situation. Thanks guys and gals!

    Thursday, October 20, 2016 10:45 AM

Answers

All replies

  • User-846834550 posted

    I am trying to figure out how to move certain columns from one table to another.

    perhaps this article would help. 

    https://social.technet.microsoft.com/wiki/contents/articles/1481.dynamic-archiving-of-tables-and-data-ms-sql.aspx 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 25, 2016 3:18 AM
  • User-718146471 posted

    I'm just seeing this, I will give this a try in the morning. It may be just what I am looking for. Thanks!

    Thursday, October 27, 2016 5:13 PM
  • User-718146471 posted

    After doing some additional research, this script appears to be creating a Cartesian product, rather than inserting the rows as they are into the table. I was looking around and instead of having 4000 rows, I ended up with over 50,000, the CVE Ids kept repeating. I may do this archival process once per year. I am thinking the most simplified way to do this would be create a table that is a snapshot of the entire previous year's data and then remove the records from last year. Let me go digging around to see what I can figure out. I thought it would be relatively simple but I can admit I was wrong about that.

    Thursday, November 3, 2016 1:14 PM
  • User-718146471 posted

    Never mind, something went totally ballistic with my data source. The archive script you've presented does work so I'll mark your answer again. Sorry about that.

    Thursday, November 3, 2016 3:14 PM