Mysql – CONCAT with multiple INSERT INTO … SELECT … WHERE

MySQL

I have a number (currently 6) of tables within multiple schemas, and I need to insert a number of (2000) rows into a table in each schema.

The idea is that I have the procedure below, and simply change the variable at the start (schema_name and user_id), and run for each table, rather than having to run multiple inserts, and change the schema name each time.

Eg, I'd change @schema_name = 'foo1' to @schema_name = 'foo2', and @user_id = '1', to user_id = '2', then re-run the script.

SET @schema_name = 'foo1';
SET @user_id = '1';

SET @proc = CONCAT('
INSERT INTO ' ,@schema_name, '.tbl_name (tbl_id, shortlist_id, year, created_at, updated_at) SELECT "2", "1", lookup_year, now(), now() FROM "lookup_tbl" WHERE lookup_id LIKE "' ,@user_id, '";
INSERT INTO ' ,@schema_name, '.tbl_name (tbl_id, shortlist_id, year, created_at, updated_at) SELECT "2", "2", lookup_year, now(), now() FROM "lookup_tbl" WHERE lookup_id LIKE "' ,@user_id, '";
INSERT INTO ' ,@schema_name, '.tbl_name (tbl_id, shortlist_id, year, created_at, updated_at) SELECT "2", "7", lookup_year, now(), now() FROM "lookup_tbl" WHERE lookup_id LIKE "' ,@user_id, '";
INSERT INTO ' ,@schema_name, '.tbl_name (tbl_id, shortlist_id, year, created_at, updated_at) SELECT "2", "9", lookup_year, now(), now() FROM "lookup_tbl" WHERE lookup_id LIKE "' ,@user_id, '";
INSERT INTO ' ,@schema_name, '.tbl_name (tbl_id, shortlist_id, year, created_at, updated_at) SELECT "2", "8", lookup_year, now(), now() FROM "lookup_tbl" WHERE lookup_id LIKE "' ,@user_id, '";
');

PREPARE stmt FROM @proc;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The problem that I'm having is that I can't appear to do multiple statements within a concat.

I had this trouble before, and used INSERT INTO (...) VALUES (A, B), (C, D)...,
but I don't think this is a option with the INSERT INTO (...) SELECT ... WHERE ...

Is there an alternative that I'm unaware of?

I've used 5 rows in the example, but I've actually got 2000 rows to do it with.

Best Answer

This is doable. What you need is 5 SELECTs with one INSERT

SET @schema_name = 'foo1';
SET @lookup_table = 'mytable';
SET @user_id = '1';

SET @proc = CONCAT('INSERT INTO ' ,@schema_name, '.tbl_name ');
SET @proc = CONCAT(@proc,'(tbl_id, shortlist_id, year, created_at, updated_at) ');
SET @proc = CONCAT(@proc,'SELECT       "2", "1", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '" ');
SET @proc = CONCAT(@proc,'UNION SELECT "2", "2", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '" ');
SET @proc = CONCAT(@proc,'UNION SELECT "2", "7", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '" ');
SET @proc = CONCAT(@proc,'UNION SELECT "2", "9", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '" ');
SET @proc = CONCAT(@proc,'UNION SELECT "2", "8", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '"');

PREPARE stmt FROM @proc;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Related Question