Arhieve and Purge the Data

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

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