CODEWITHSUNDEEP
mysqlstored-proceduresprepare
Crimson
Crimson

Reputation: 1

MySQL Stored Procedure that Calls a Table w/ dynamic Name

I have a stored procedure in MySQL that calls a function that inserts values into a table. The insert statement is like

INSERT INTO Table1 SELECT * from Table2

Table2 has a variable name, depending on the date the procedure is called, like table_201410 , if the procedure was called in October 2014. Soevery month, the procedure should make the select from another table.

I have read some answers about preparing and executing, however as I am new to MySQL/Programming. I am a bit lost.

Upvotes: 0

Views: 578

Answers (1)

Ivan Cachicatari
Ivan Cachicatari

Reputation: 4284

You can use a prepared statement like this:

DELIMITER $$
DROP PROCEDURE copy_table $$
CREATE PROCEDURE copy_table ()
BEGIN

    set @table_name = date_format(now(),'%Y%m'); -- getting year and month

    -- concatenating
    set @sql =  concat('INSERT INTO Table1 SELECT * from table_',@table_name);


    -- creating a prepared statement and executing
    PREPARE insert_stmt FROM @sql;
    EXECUTE insert_stmt;
    DEALLOCATE PREPARE insert_stmt;
END$$

DELIMITER ;

Upvotes: 2

Related Questions