0

I want to create a SQL script for MySQL 5.7 that inserts data from a table of a database origin into a table of another target database.

I want to have this source-database defined by a variable.

USE my_target_db;
SET @origin_db='my_origin_db';
SET @origin_table = concat(@origin_db,'.','tablename');

INSERT INTO target_table SELECT * FROM @origin_table;

Variables are used in various example to define column names but I never seen a way to define a table with it.

Is anyone has a trick for this ?

Orden
  • 589
  • 3
  • 13
  • 1
    Variables won't use in table name in MySQL – Maksym Fedorov Dec 13 '18 at 13:51
  • 1
    You would probably have to use a prepared statement: https://stackoverflow.com/questions/8809943/how-to-select-from-mysql-where-table-name-is-variable – Tony Dec 13 '18 at 13:51
  • Possible duplicate of [How to select from MySQL where Table name is Variable](https://stackoverflow.com/questions/8809943/how-to-select-from-mysql-where-table-name-is-variable) – unutbu Dec 13 '18 at 14:15

2 Answers2

1

Variables won't use in table name in MySQL. You only can use a prepared statement for dynamic build query. For example:

USE my_target_db;
SET @origin_db='my_origin_db';
SET @origin_table = CONCAT(@origin_db,'.','tablename');
SET @query = CONCAT('INSERT INTO target_table SELECT * FROM ', @origin_table);

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

You can read more detail about it in official documentation

Maksym Fedorov
  • 6,383
  • 2
  • 11
  • 31
  • @Orden i hope `SET @origin_db='my_origin_db';` will not be set by the client from untrusted user input like a webapplication... Otherwise this answer is unsafe to use because off possible blind SQL injection attack – Raymond Nijland Dec 13 '18 at 14:33
1

You can use Prepared Statement like this:

USE my_target_db;
SET @origin_db='my_origin_db';
SET @origin_table = concat(@origin_db,'.','tablename');

SET @qry1 = concat('INSERT INTO target_table SELECT * FROM ', @origin_table);
PREPARE stmt1 from @qry1;
EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;
Alexey
  • 1,521
  • 1
  • 13
  • 24
  • @Orden i hope `SET @origin_db='my_origin_db';` will not be set by the client from untrusted user input like a webapplication... Otherwise this answer is unsafe to use because off possible blind SQL injection attack – Raymond Nijland Dec 13 '18 at 14:33
  • @RaymondNijland good remark, but filtering of user input is a task for an application that used a database – Maksym Fedorov Dec 13 '18 at 14:41
  • @MaximFedorov mine point of comment should be into using `PREPARE <> FROM ''; EXECUTE <> USING @` with is safe against SQL injections.. You can atleast query the information_schema.DATABASES that way to test of it's safe and use that query result in the insert query.. Besides indeed the application would use prepared statements to protect against MySQL injections MySQL native seams to support [this](https://www.db-fiddle.com/f/os9FnWYAJNLfb4CH2gqBbE/1) so iam pretty sure prepared staments in anny programming language can also prepare `SET` queries. – Raymond Nijland Dec 13 '18 at 14:55