[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to archive table in SQL Server

Posted on 2009-05-04
4
Medium Priority
?
2,092 Views
Last Modified: 2012-05-06
How to archive table in SQL Server every 3 months?
0
Comment
Question by:easy_life
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 900 total points
ID: 24301032
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
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 600 total points
ID: 24301057
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24301306
chapman is absolutely correct, sql job is a good option, you can use SSIS packages as well.
0
 

Author Closing Comment

by:easy_life
ID: 31577836
Thank you for your help.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question