Arhieve and Purge the Data

Posted by Anonymus under Sql Server on 8/27/2012 | Points: 10 | Views : 758 | Status : [Member] | Replies : 2
Hi,
I need to write a procedure to archieve and purge the data from a table older than 30days.

Could any one please help me on this?

Regards,
Anonymus



Responses

Posted by: Atal.Upadhyay on: 8/27/2012 [Member] Starter | Points: 25

Up
0
Down
First thing, you need to have timestamp field in the table to do so.

Second, you need to create a procedure or may be you can create Sql Job to run periodically like this.

CREATE PROCEDURE prc_clean_tables ()
BEGIN
BEGIN TRANSACTION;

DECLARE _now DATETIME;
SET _now := NOW();

INSERT
INTO Purge_Table
SELECT *
FROM MainTable
WHERE timestamp < _now - 30;


DELETE
FROM MainTable
WHERE timestamp < _now - 30;


COMMIT;
END



Anonymus, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Anonymus on: 8/28/2012 [Member] Starter | Points: 25

Up
0
Down
Hi ,
Thanks for this.

Just one question. You said I need to create a store proc or a sql job...does this mean that I do not need a store procedure if i create a sql job.? Basically I will be creating a sql job that will be purging the data of 30 days older.

Also, my table does not have timestamp column.

Please guide me through....i am very new to all this...

Thank You...much appreciated..

Regards,
Anonymus

Anonymus, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response