mysql (5.1) > create table with name from a variable

Question

I'm trying to create a table with a name based on the current year and month(2011-09), but MySQL doesn't seem to like this.

SET @yyyy_mm=Year(NOW())+'-'+Month(NOW());
CREATE TABLE `survey`.`@yyyy_mm` LIKE `survey`.`interim`;
SHOW TABLES IN `survey`;

+-----------+
| interim   |
+-----------+
| @yyyy_mm  |
+-----------+

If I do CREATE TABLE; without the ticks around @yyyy_mm, I get a generic syntax error.

@yyyy_mm resolves to 2020.

Identifiers are not variables. Variables are not identifiers. This requires the use of "dynamic SQL" -- it's icky in all databases I've worked with. — user166390
– user166390, Commented Sep 20, 2011 at 17:25
See stackoverflow.com/questions/190776/… and stackoverflow.com/questions/929244/… and stackoverflow.com/questions/5530755/… — user166390
– user166390, Commented Sep 20, 2011 at 17:27

nos · Accepted Answer · 2011-09-20 17:38:35Z

36

You should be able to do something like this:

SET @yyyy_mm=DATE_FORMAT(now(),'%Y-%m');
SET @c = CONCAT('CREATE TABLE `survey`.`',@yyyy_mm, '` LIKE `survey`.`interim`');
PREPARE stmt from @c;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

edited Sep 20, 2011 at 17:38

answered Sep 20, 2011 at 17:33

nos

231k60 gold badges436 silver badges516 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nicola Cossu · Accepted Answer · 2011-09-20 17:33:41Z

8

set @yyyy_mm=concat(year(now()),'-',month(now()));
set @str = concat('create table survery.`', @yyyy_mm,'` like survey.interim;');
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;

answered Sep 20, 2011 at 17:33

Nicola Cossu

56.7k15 gold badges96 silver badges99 bronze badges

Collectives™ on Stack Overflow

mysql (5.1) > create table with name from a variable

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related