Hi,
I need to provide a way for archiving data from an SQL Server database. I want to split the data from one table into a separate table for each year based on a date field in the table and then delete it from the source table. (Is there also a log table that I need to delete or archive too?)
I want to let a user enter a date that they want to archive up to (that is all records with the date less than the entered date will be archived).
I can think of two options as to how to approach this:
1. Use my business object to get a cursor with all the records to be archived, then process them one at a time spitting them into the appropriate "year" table and then deleting them from the main table.
2. Use a stored procedure.
I don't know anything about stored procedures in SQL Server, but I have a feeling that a stored procedure should be faster than using my business objects.
Does anybody have any suggestions on what the best way is to approach this and maybe where I can find out more information?
Thanks.