Mysql UNION with dynamic query

i have the following problem. I inherited a software that uses a database prefix for every customer. All tables have the same structure and columns. For a data migration to new version i want to union all these tables and set a customer foreign key instead and get rid of all the subtables. i'm looking for a way to create a view for this task because i also want to stay backwards compatible for now. I found this dynamic query which seems to do what i want but i can't execute on my mysql server. I assume it was written for another sql server.

The table name structure is (about 80 customer tables): customer1_faxe customer2_faxe customer3_faxe customer4_faxe ...

How would you approach this problem?

DECLARE  @SelectClause  VARCHAR(100)    = 'SELECT *'
        ,@Query         VARCHAR(1000)   = '' 

SELECT @Query = @Query + @SelectClause + ' FROM ' + TABLE_NAME + ' UNION ALL '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%_faxe'

SELECT @Query = LEFT(@Query, LEN(@Query) - LEN(' UNION ALL '))

EXEC (@Query)


ANSWERS:


This query is using SQL Server syntax. You need something like this:

declare @SelectClause varchar(8000);
declare @Query varchar(8000);

set @SelectClause = 'SELECT *';

SELECT @Query := group_concat(@SelectClause, ' FROM ', TABLE_NAME SEPARATOR ' UNION ALL ')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%_faxe';

prepare stmt from @Query;
execute stmt;

Note that the group_concat() with separator simplifies the logic.



 MORE:


 ? MYSQL: UNION results between two tables where omitting records from first table if PK found in second table
 ? How can I leverage an ORM for a database whose schema is unknown until runtime?
 ? How can I leverage an ORM for a database whose schema is unknown until runtime?
 ? How can I leverage an ORM for a database whose schema is unknown until runtime?
 ? Which ORM can preserve the correspondence between db schema and domain model?
 ? how to minimize application downtime when updating database and application ORM
 ? Can we use MongoDB with ORMs we used to use with relational databases, such as linq2sql, entity framework, subsonic,...?
 ? How to use multilanguage database schema with ORM?
 ? Please suggest one .Net ORM, which supports runtime schema evolution
 ? Can the code-first features of EF 4.1 and greater be used when the DB schema is unknown until runtime?