With the following procedure, it is possible to move data from a primary table to a secondary table for archival purposes. This specific example moves data on the basis of a date column in the primary table. The number of days (related to the current date/time) to remain in the primary table is passed to the procedure. Start procedure with ‘CALL pCleanUPTable (35)’.
DELIMITER //
CREATE PROCEDURE pCleanUpTable(IN iDays INT)
BEGIN
START TRANSACTION;
SELECT (now() - interval iDays day) INTO @dtDeleteFrom;
SAVEPOINT PreCleanUP;
-- Copy old records to new archive/secundary table...
INSERT INTO sectab (primtab_id, value1, value2, value3, datum)
SELECT id, value1, value2, value3, datum from primtab where datum < @dtDeleteFrom;
SELECT ROW_COUNT() INTO @iRowsCopy;
-- Remove records from primary table...
DELETE FROM primtab WHERE datum < @dtDeleteFrom;
SELECT ROW_COUNT() INTO @iRowsDelete;
-- Check if rows copied -eq rows deleted...
IF (@iRowsCopy = @iRowsDelete) THEN
COMMIT;
SELECT 'Success - Committed!' AS Result, @iRowsCopy AS RowsCopy, @iRowsDelete AS RowsDelete;
ELSE
ROLLBACK TO PreCleanUp;
SELECT 'Failure - ROLLBACK!' AS Result, @iRowsCopy AS RowsCopy, @iRowsDelete AS RowsDelete;
END IF;
END //
DELIMITER ;
In bovenstaande voorbeeld is gebruik gemaakt van de volgende InnoDB tabellen.
[primtab]
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| value1 | varchar(75) | YES | | NULL | |
| value2 | varchar(75) | YES | | NULL | |
| value3 | varchar(75) | YES | | NULL | |
| datum | datetime | YES | | NULL | |
+--------+------------------+------+-----+---------+----------------+
[sectab]
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| primtab_id | int(10) | YES | | NULL | |
| value1 | varchar(75) | YES | | NULL | |
| value2 | varchar(75) | YES | | NULL | |
| value3 | varchar(75) | YES | | NULL | |
| datum | datetime | YES | | NULL | |
+------------+------------------+------+-----+---------+----------------+