MySQL Variables storing database name

Question

I have a long script that I need to run on several different databases (all witht he same tables and field names).

What I would like to do is something like this:

1  SET @TARGET_DATABASE = 'beta'
2  SET @SOURCE_DATABASE = 'sandbox';
3  
4  CREATE DATABASE IF NOT EXISTS @TARGET_DATABASE;
5  USE @TARGET_DATABASE;

...

10 INSERT INTO `tableFoo` SELECT * FROM @SOURCE_DATABASE.`tableFoo`;

On line 10 I get an error (I'm not surprised): "Script line: 10 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 '@SOURCE_DATABASE.tableFoo' at line..."

So I tried this insted for line 10:

10 SET @TABLE=CONCAT('`',@SOURCE_DATABASE,'`','.`tableFoo`');
11 INSERT INTO `tableFoo` SELECT * FROM @TABLE;

...and again error "Script line: 11 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 '@TABLE' at line..." As you can see, line 10 works fine...

Is there any way to refer to a table in a database with a variable?

// Thank you.

Arash Mousavi · Accepted Answer · 2013-07-17 13:44:26Z

2

It will work if you concat the entire query. Try:

set @db = 'mydb';
set @tble = 'table';

set @query = concat('INSERT INTO tablefoo SELECT * FROM ', @db, '.', @tble);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

edited Jul 17, 2013 at 13:44

Arash Mousavi

2,1465 gold badges28 silver badges48 bronze badges

answered Apr 3, 2011 at 17:05

blankabout

2,6473 gold badges20 silver badges29 bronze badges

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

1 Comment

Max Kielland Over a year ago

I have not tested your solution yet. I had to move on and solved it the same way I did so far. I get back when I have tested it.

Collectives™ on Stack Overflow

MySQL Variables storing database name

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related