moved archived data | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

moved archived data

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
lkarthik
If you have a date column in that table which records the time that record is loaded into the table, then this can help you… INSERT INTO ArchiveDB..TableName
SELECT * FROM YourDB..TableName
WHERE DateColumnName < getdate() – 5 and drop those rows from the main table Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
I want to move archived data hour by hour lkarthik
Hi all
I have fixed. tabledate < DateAdd(hour,-1,getdate())
and scheduled the job hourly basis.. Regards
lkarthik
Something about ramkumar’s post makes me nervous. If you use:
INSERT INTO ArchiveDB..TableName
SELECT * FROM YourDB..TableName
WHERE DateColumnName < getdate() – 5 and then something like DELETE YourDb..TableName
WHERE DateColumnName < getdate() – 5 I think there is a risk of losing a few records if you call GetDate() twice. Time will elapse between executing the two statements. I know that you are using it hourly, but are you somehow locking YourDB..TableName? I would suggest setting a variable for the date(or time) at the beginning. DECLARE @ArchivePoint datetime; SET @ArchivePoint = DateAdd(hour,-1,getdate())
INSERT INTO ArchiveDB..TableName
SELECT * FROM YourDB..TableName
WHERE DateColumnName < @ArchivePoint DELETE YourDb..TableName
WHERE DateColumnName < @ArchivePoint Jason Akin
CUInterface, LLC
]]>