How to Retain Old Data and Add New

[ghildebrand]

I'm using a database where the current tables only hold 6 months of data before moving to an archive table. I'm pulling from a current table, and would like to keep the older data while adding new data.

For example, the current table holds data from 9/24/19 to 3/24/20. I'd like to pull all of that today. When I pull the data again on 4/24/20, I'd like to have all the data from 9/24/19 to 4/24/20. What is the best way to keep the old data that I've already pulled, and add the current/new data from the current table?

[DavePrinsloo · Accepted solution]

It would probably  be  best to keep the data in a partitioned form.   I.e . You would have a different  physical table per month.   This allows you to drop the oldest each month without touching the  other 11 months.   Then create new table as an extract of the last month.   Alternatively,  you can keep a copy of the last 6 months of the archive in monthly  parts  and append them to a current extract of the active data.

[ghildebrand]

Thank you!