Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

need to archive data for every 6 months

Posted on 2013-05-22
10
Medium Priority
?
220 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
[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
  • 7
  • 3
10 Comments
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 39189998
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 13

Expert Comment

by:Koen Van Wielink
ID: 39189999
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 13

Accepted Solution

by:
Koen Van Wielink earned 2000 total points
ID: 39190000
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sqlcurious
ID: 39190212
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 13

Expert Comment

by:Koen Van Wielink
ID: 39190241
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 13

Expert Comment

by:Koen Van Wielink
ID: 39190242
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 13

Expert Comment

by:Koen Van Wielink
ID: 39190243
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
ID: 39194576
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 13

Expert Comment

by:Koen Van Wielink
ID: 39194953
What's the error you're getting?
0
 

Author Closing Comment

by:sqlcurious
ID: 39248466
thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

715 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