Deleting historical data from a large table in SQL.

Question

Hello,
I've 5 different tables which are archive tables(Production Database)and I would like to delete records older than 5 years and move to different database(Archive Database) for those records so I can able to access in future as needed.
Is there way to do it?

Answer 1

What is the AddDate data type? Most likely you need to use
select * from tbl where AddDate <= dateadd(year,-5, CURRENT_TIMESTAMP)

Answer 2

Based on your question I understand you want to copy the records older than five year to another database. right? I believe you have some timestamps in your fields so that you can identify the records older than 5 years, normally you may have created date, or last modified date that could be your column to decide the records that need to be archived.

In order to automate the data archival process, you can create an SQL Server Job using the SQL Server agent.

Reference
https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-job?view=sql-server-ver15
https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-transact-sql-job-step?view=sql-server-ver15

In the SQL server job, you can add your query, something similar to the following.

INSERT INTO TargetDatabase.Table (field1, field2, field3) SELECT field1, field2, field3 FROM SourceTable WHERE (some condition to determine the record is 5 year old )

You may schedule the SQL server job to execute on a certain time of a day.
Refer: https://learn.microsoft.com/en-us/sql/ssms/agent/create-and-attach-schedules-to-jobs?view=sql-server-ver15

Hope this helps

Answer 3

As Sreejukg said, yes, you can do it, provided that there is a way to identify the rows that are more then five years old. If there is not, it's starting to get tricky.

Assuming that you know how to find which rows to archive, what is really your question? Do you want to us to write the INSERT and DELETE statements for you? Or are you asking if there are any special considerations you need to make?

I ask these questions, because if just type something up, I may be answering a different question than you had in mind.