Mysql procedure to move data from one database to another perodically

Question

I am trying to accomplish following thing dynamically.

Basic goal is moving records from first database table to second database table. Database table names always change due to rotation. Also records get added periodically in first table, so periodically I want to call this procedure with arguments first table, second table and special db1 id from last element of second table. So using this id, i can only insert new entries.

For this I am trying to write procedure like this. Database is mysql.

DROP PROCECURE IF EXISTS `foo_bar`;

DELIMITER $$

CREATE PROCEDURE `foo_bar`(IN var_dbtable1 varchar(50), IN var_dbtable2 varchar(50))
BEGIN

    --get db1_id of last record from second table    
    DECLARE v_lst_db1_id bigint(21);

    SET @query1 = CONCAT("SELECT id INTO ", v_lst_db1_id, " FROM ", var_dbtable2, " ORDER BY id DESC LIMTI 1");
             PREPARE stmt1 from @query1;
             EXECUTE stmt1;
             DEALLOCATE PREPARE stmt1;

    -- insert records in db2 from db1 whose ids are greater than last id.
    SET @query2 = CONCAT("INSERT INTO ", var_dbtable2, "(col1, col2, col3)
            SELECT co1, col2, col3
            FROM ", var_dbtable1, " WHERE id > ", v_lst_db1_id, "");
            PREPARE stmt2 from @query2;
            EXECUTE stmt2;
            DEALLOCATE PREPARE stmt2;

END $$

DELIMTER ;

Later i want to call this procedure

call foo_bar("foo.foo1", "bar.bar1");

But this procedure seems faulty. Particularly part of first query. Is I am missing something?

Thanks in advance.

Update: Error Message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

Answer 1

Try like this:

SET @query1 = CONCAT("SELECT id INTO v_lst_db1_id FROM ", var_dbtable2, " ORDER BY id DESC LIMTI 1");
         PREPARE stmt1 from @query1;
         EXECUTE stmt1;
         DEALLOCATE PREPARE stmt1;

You want to use the variable, not the value the variable is holding. In this case null.

Your query would've been

SELECT id INTO NULL FROM ...