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.

Bensky

Answer 1

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;

Tungting

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. – Bensky