moved archived data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

moved archived data

Author

Topic

leynakarthik
Starting Member

7 Posts

Posted - 2006-06-29 : 00:56:57

Hi gurus,

I have one large table in sqlserver database that table containing 5 millions records. I want to move the records to archive table hour by hour older than five days. Please help me
to solve this issue.

Regards

leynakarthik

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-29 : 01:03:27

something like this. Specify the start and end date time range in the variable. Just loop the query and change the start / end datetime

insert into archive ( . . . )
select . . .
from   table
where  datecol >= @start_datetime
and    datecol <= @end_datetime

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-29 : 01:46:10

or

insert into archive ( . . . )
select . . .
from table
where datecol <= DateAdd(day,-5,getdate())

Madhivanan

Failing to plan is Planning to fail

leynakarthik
Starting Member

7 Posts

Posted - 2006-06-29 : 02:03:42

I want to move the archived data hour by hour

leynakarthik

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-29 : 02:10:40

quote:
Originally posted by leynakarthik

I want to move the archived data hour by hour

leynakarthik

Schedule it as JOB

Madhivanan

Failing to plan is Planning to fail

leynakarthik
Starting Member

7 Posts

Posted - 2006-06-29 : 03:00:05

Hi all
I have fixed.

tabledate < DateAdd(hour,-1,getdate())
and scheduled the job hourly basis..

Regards

leynakarthik

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-29 : 05:31:14

quote:
Originally posted by leynakarthik

Hi all
I have fixed.

tabledate < DateAdd(hour,-1,getdate())
and scheduled the job hourly basis..

Regards

leynakarthik

Congratulations! Now you are moving data that is one hour old to the storage.
Use

tabledate < DateAdd(dd, -5, getdate())

instead.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources