• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2093
  • Last Modified:

How to archive table in SQL Server

How to archive table in SQL Server every 3 months?
0
easy_life
Asked:
easy_life
2 Solutions
 
chapmandewCommented:
This is kind of an open-ended question, but I would use a SQL Agent job, schedule it to run nightly, and have it call a procedure that takes the data from your table that is over 3 months old and puts it into an archive table of the same structure.  

procedure code:

create procedure usp_archivedata
as
begin
insert into archivetable(field1, field2, field3)
select field1, field2, field3
from tablename
where datefield <= dateadd(m, -3, getdate())
end
go


information on sql jobs:
http://msdn.microsoft.com/en-us/library/ms191439.aspx
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
here is another option, partition the table  (only available on Enterprise edition
http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx
http://weblogs.sqlteam.com/dmauri/archive/2005/07/18/7147.aspx
0
 
RiteshShahCommented:
chapman is absolutely correct, sql job is a good option, you can use SSIS packages as well.
0
 
easy_lifeAuthor Commented:
Thank you for your help.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now