1

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.

0

1 Answer 1

1

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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.