MySQL: Using variable with create and insert

How can I use a variable with "create" and "insert" statements?

This does not work:
SET @now := (SELECT DATE_FORMAT(NOW(),'tbl_%Y_%m_%d_%H_%i'));
CREATE TABLE (SELECT @now) LIKE tbl;
INSERT (SELECT @now) SELECT * FROM tbl;

Open in new window

LVL 16
hankknightAsked:
Who is Participating?
 
Chris StanyonCommented:
You'll need to CONCAT the variable into a statement and then execute that:

SET @newTable := (SELECT DATE_FORMAT(NOW(),'tbl_%Y_%m_%d_%H_%i'));

SET @sql := CONCAT('CREATE TABLE ', @newTable,' LIKE tbl');
PREPARE stmt FROM @sql; 
EXECUTE stmt;

SET @sql := CONCAT('INSERT INTO ', @newTable, ' SELECT * FROM tbl');
PREPARE stmt FROM @sql; 
EXECUTE stmt;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.