>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.
A stored procedure would be faster but you might not need it. SQL server has partioning capabilities. Check partition in books online (and it's been enhanced in SQL 2005).
Cetin