Solved

need to archive data for every 6 months

Posted on 2013-05-22
10
212 Views
Last Modified: 2013-06-14
Hi Experts,
I need to archive data from a table(Original Table ) into a new table(Archive Table) every six months.
For example: Archive table after year should have 1 year data + past 6 months data
So how should I have the query such that it would be picking data dynamically and also checks and makes sure its not duplicating data, please suggest how we need to have the table structure of the archive data ?
So Original table has a jobid which is an unique id. Structure of Original table:
JobID | CustNo | OpenDate | CloseDate
0
Comment
Question by:sqlcurious
  • 7
  • 3
10 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
I'd go with a table structured in the same way, just add an ArchiveDate column with the date of archiving.
Your query would look something like this:

Insert into JobsArchive
(	JobID
,	CustNo
,	OpenDate
,	CloseDate
,	ArchiveDate
)

Select	a.*
, getdate()
from JobsTable a
where	not exists
	(select	1
	from	JobsArchive b
	where	a.JobID = b.JobID)

Open in new window


You can schedule a job every 6 months that executes this query.
It would be good if besides the "not exists" check you could add a check on dates as well because that would reduce the number of records that have to be read over time. Your archive will grow and grow, and if all records have to be checked each time this will take longer and longer. For instance, if your opendate is chronological, you could add:

Insert into #JobsArchive
(	JobID
,	CustNo
,	OpenDate
,	CloseDate
,	ArchiveDate
)

Select		a.*
		,	getdate()
from #JobsTable a
where	not exists
	(select	1
	from	#JobsArchive b
	where	a.JobID = b.JobID)
and	a.opendate >= (select max(opendate)
					from #jobsArchive c)

Open in new window

0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
I'd go with a table structured in the same way, just add an ArchiveDate column with the date of archiving.
Your query would look something like this:

Insert into JobsArchive
(	JobID
,	CustNo
,	OpenDate
,	CloseDate
,	ArchiveDate
)

Select	a.*
, getdate()
from JobsTable a
where	not exists
	(select	1
	from	JobsArchive b
	where	a.JobID = b.JobID)

Open in new window


You can schedule a job every 6 months that executes this query.
It would be good if besides the "not exists" check you could add a check on dates as well because that would reduce the number of records that have to be read over time. Your archive will grow and grow, and if all records have to be checked each time this will take longer and longer. For instance, if your opendate is chronological, you could add:

Insert into #JobsArchive
(	JobID
,	CustNo
,	OpenDate
,	CloseDate
,	ArchiveDate
)

Select		a.*
		,	getdate()
from #JobsTable a
where	not exists
	(select	1
	from	#JobsArchive b
	where	a.JobID = b.JobID)
and	a.opendate >= (select max(opendate)
					from #jobsArchive c)

Open in new window

0
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 500 total points
Comment Utility
I'd go with a table structured in the same way, just add an ArchiveDate column with the date of archiving.
Your query would look something like this:

Insert into JobsArchive
(	JobID
,	CustNo
,	OpenDate
,	CloseDate
,	ArchiveDate
)

Select	a.*
, getdate()
from JobsTable a
where	not exists
	(select	1
	from	JobsArchive b
	where	a.JobID = b.JobID)

Open in new window


You can schedule a job every 6 months that executes this query.
It would be good if besides the "not exists" check you could add a check on dates as well because that would reduce the number of records that have to be read over time. Your archive will grow and grow, and if all records have to be checked each time this will take longer and longer. For instance, if your opendate is chronological, you could add:

Insert into #JobsArchive
(	JobID
,	CustNo
,	OpenDate
,	CloseDate
,	ArchiveDate
)

Select		a.*
		,	getdate()
from #JobsTable a
where	not exists
	(select	1
	from	#JobsArchive b
	where	a.JobID = b.JobID)
and	a.opendate >= (select max(opendate)
					from #jobsArchive c)

Open in new window

0
 

Author Comment

by:sqlcurious
Comment Utility
Thanks a lot  Kvwielink, if I have to use just a query and not a job how would I do it? use dateadd? please suggest?
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Hi Sqlcurious,

The query I posted would just check for any new records in your job table that don't exist in the archive table yet. Not sure what you wish to use Dateadd for.
If you don't use a scheduled job you'd have to run this query manually every 6 months. I'd just schedule a job in the SQL Server Agent so that you don't have to worry about it anymore. Have the job execute every 6 months, and all your new records should be inserted into your archive table without interference.

Rgds,

Kvwielink
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Hi Sqlcurious,

The query I posted would just check for any new records in your job table that don't exist in the archive table yet. Not sure what you wish to use Dateadd for.
If you don't use a scheduled job you'd have to run this query manually every 6 months. I'd just schedule a job in the SQL Server Agent so that you don't have to worry about it anymore. Have the job execute every 6 months, and all your new records should be inserted into your archive table without interference.

Rgds,

Kvwielink
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Hi Sqlcurious,

The query I posted would just check for any new records in your job table that don't exist in the archive table yet. Not sure what you wish to use Dateadd for.
If you don't use a scheduled job you'd have to run this query manually every 6 months. I'd just schedule a job in the SQL Server Agent so that you don't have to worry about it anymore. Have the job execute every 6 months, and all your new records should be inserted into your archive table without interference.

Rgds,

Kvwielink
0
 

Author Comment

by:sqlcurious
Comment Utility
Hi Kvwielink,
I am having errors in this part, not sure why this isnt working, seems right to me, please help
where not exists
      (select      1
      from      #JobsArchive b
      where      a.JobID = b.JobID)
and      a.opendate >= (select max(opendate)
                              from #jobsArchive c)
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
What's the error you're getting?
0
 

Author Closing Comment

by:sqlcurious
Comment Utility
thanks
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now