Archival stored procedures
<p>Good morning everyone, I am trying to figure out how to move certain rows from one table to another. Here is the kicker; each row 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 rows from the previous month (for example D201609##) and move them to an archive table (copy then remove the copied rows ). </p>

<p>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.</p>

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

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

<p>I figure for the yearly archive table, I can simply create a procedure 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.</p>