Thanks Cetin,

I'll check out partitioning.

>>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