Using a SP to split up a table

Lets assume that we have one big table with forum posts (called forum_posts) from quite a long period. We want to split this big table up in several smaller tables for every month. The date is stored in a datetime column called dt.

Here's a stored procedure that splits the table up in smaller tables called fp_YYMM which are identical in its structure as the original table. It uses a cursor to walk through every month, create the new table (with a CREATE TABLE ... LIKE statement) and copying the correct data into the archive tables. The original table will persist.

The procedure also uses prepared statements to dynamically build up the SQL statements for creating the new tables and copying the contents.

DELIMITER //

DROP PROCEDURE IF EXISTS splitUpTables //
CREATE PROCEDURE splitUpTables ()
BEGIN
  declare finished int default 0;
  declare month char(7);
  declare month2 char(6);
  declare dfrom date;
  declare dto date;

  declare curCreate cursor for 
     SELECT distinct date_format(dt, '%Y-%m') as month 
     FROM forum_ posts order by date_format(dt, '%Y-%m');
  declare continue handler for not found set finished := 1;

  open curCreate;

  fetch curCreate into month;

  while finished = 0 do

    select concat(substring(month, 1, 4), substring(month, 6, 2)) 
       into month2;

    select month2;

    set @sql := concat('create table if not exists fp_', month2, 
                       ' like forum_posts ');
    prepare pCreate from @sql;
    execute pCreate;

    set dfrom := cast(concat(month, '-01') AS date);
    select date_sub(date_add(dfrom, interval 1 month), interval 1 day) 
       into dto;

    set @sql1 := concat('truncate table fb_', month2);
    set @sql2 := concat('insert into fp_', month2, 
       ' select * from forum_posts where dt between \'', 
       dfrom, '\' and \'', dto, '\' order by dt');

    prepare pTruncate from @sql1;
    execute pTruncate;
    prepare pInsert from @sql2;
    execute pInsert;

    fetch curCreate into month;

  end while;

  close curCreate;

END //

DELIMITER ;