2

I have a table that stores what database should I use for a given device( specified by its IMEI), like this:

imeiDB(
IMEI VARCHAR(15),
db VARCHAR(100));

Let's say I have the database device1(IMEI:123456789123456) is using stored in imeiDB:

123456789123456 | device1db

Supposing I have not selected the database to store the data, how can I select it on the insert into query of my table device1db.table1?

table1(IMEI VARCHAR(15),data VARCHAR(10))

I tried this but it did not work:

INSERT INTO (SELECT db FROM imeiDB where IMEI=123456789123456).table1 (IMEI,data) VALUES (123456789123456,"somestuff")

Any suggestions?

MagisterMundus
  • 185
  • 1
  • 5

1 Answers1

1

What you need is some Dynamic SQL.

STEP 01 : Retrieve the DB

SET @IMEI = 123456789123456;
SELECT db INTO @db FROM imeiDB WHERE IMEI = @IMEI;

STEP 02 : Create the SQL to do the INSERT into the retrieve DB

SET @sql = CONCAT('INSERT INTO ',@db,'.table1 (IMEI,data) VALUES (',@IMEI,',"somestuff"');
SELECT @sql\G

STEP 03 : Execute the SQL Statement you just created

PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

EPILOGUE

Execute them in that order

SET @IMEI = 123456789123456;
SELECT db INTO @db FROM imeiDB WHERE IMEI = @IMEI;
SET @sql = CONCAT('INSERT INTO ',@db,'.table1 (IMEI,data) VALUES (',@IMEI,',"somestuff"');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

GIVE IT A TRY !!!

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • Thank you, it works. But can you tell me why it does not work in a single "multi-statement" query? – MagisterMundus Jun 05 '15 at 17:03
  • MySQL will only process SQL as a Static Statement. 2) The MySQL Query Optimizer has to reach into the information_schema as part of the parsing of the SQL. That's the only dynamic part MySQL plays on its own. In all other instances, you have to customize SQL statements with pitch-perfect syntax. As an example, see my post http://dba.stackexchange.com/questions/1018/mysql-drop-table-starting-with-a-prefix/1369#1369 on how I delete multiple tables using a Stored Procedure that uses Dynamic SQL and how I did the same with just brute force Dynamic SQL.
    • – RolandoMySQLDBA Jun 05 '15 at 17:15