Styjun

My goal is to merge tables from different schema into one single schema so I would like to execute this query:

INSERT INTO inventory.maintenance
SELECT *
FROM (
 SELECT NULL, 'A' as department_id, maintenance.* FROM a_inventory.maintenance
 UNION
 SELECT NULL, 'B' as department_id, maintenance.* FROM b_inventory.maintenance
 UNION
 SELECT NULL, 'E' as department_id, maintenance.* FROM e_inventory.maintenance
 UNION
 SELECT NULL, 'L' as department_id, maintenance.* FROM l_inventory.maintenance
 UNION
 SELECT NULL, 'M' as department_id, maintenance.* FROM m_inventory.maintenance
) AS tmp_maintenance

I have to do this query for many different tables and all tables are not in all departments. Instead of writing each queries manually, I would like to add a bit of automation...

SET @department = ('A', 'B', 'E', 'L', 'M');
SET @connection = 'inventory';
SET @table = 'maintenance';

/* MAGIC HERE to create @union */

SET @s = CONCAT(
 'INSERT INTO ', @connection, '.', @table,
 'SELECT * FROM (', @union, ') AS tmp');

PREPARE stm1 FROM @s; EXECUTE stm1; DEALLOCATE PREPARE stm1; 

I would like to create @union from @department. I have tried to use REPEAT, but it does not really work.

One possible way to solve this problem. But you need to have a table departments somewhere from which you can extract the id assuming the id is a char:

SET @department = 'A,B,E,L,M' COLLATE utf8mb4_unicode_ci;
SET @connection = 'inventory';
SET @table = 'maintenance';

SET @union = '';
SELECT @union := CONCAT(
 @union, 
 ' SELECT NULL, "', id , '", ', 
 @table,'.* FROM ', id, '_', 
 @connection, '.', @table, 
 ' UNION '
)
FROM departments WHERE FIND_IN_SET(id, @department);

SELECT @union := SUBSTRING(@union, 1, LENGTH(@union) - LENGTH('UNION '));

SET @s = CONCAT(
 'INSERT INTO ', @connection, '.', @table, ' '
 '( SELECT * FROM (', @union, ') AS tmp)');

SELECT @s;

PREPARE stm1 FROM @s; EXECUTE stm1; DEALLOCATE PREPARE stm1;