How to merge multiple tables with similar names in MySQL

Question

Possible Duplicate:
How can I merge two MySql tables?

I want to merge multiple tables that have the same structure and make one large table. The tables have similar names, so I want to use the LIKE statement. Can anyone tell me how I can do this?

The tables are very simple, each having an ID column and a few other columns, but there are a large amount of tables, all of which have names like 'TX-xxx', where 'TX' means Texas, and 'xxx' are the counties in Texas; you know there are more than 200 counties in Texas. (In fact, I have to do this for all the states.) So I want to use the statement "LIKE 'TX-___'".

Thanks!

You might wanna provide some structure of your table + sample desired resultset. — J A, May 23 '12 at 17:03
look here: [How can I merge two MySql tables?](http://stackoverflow.com/questions/725556/how-can-i-merge-two-mysql-tables) — pearcoding, May 23 '12 at 17:03

score 5 · Accepted Answer · edited May 23 '17 at 12:03

You would have to give more information so we know exactly what you want but you could create a view

CREATE VIEW myViewName AS 

select * 
from table1 

union all 

select * from 
table2

This way it would show the information from all your tables (and can be limited so in the selects to not show everything) and when table1, table2, etc are changed the view will reflect this. You can change it at anytime and fetch from it as you would a table:

 select * from myViewName

Now for grabbing from specific tables I am not sure how you can do this in mysql though I have done it in tsql. This previous question would help you so you might have something like:

-- Create temporary table of varchar(200) to store the name of the tables. Depending on how you want to go through the array maybe an id number (int). 
insert into tempTableName (name)
SELECT table_name FROM information_schema.tables WHERE table_schema = 'database_name' and table_name like 'TX_%';
declare @sqlQuery varchar(max)
--Then you will want to loop through the array and build up an sql statement
-- For each loop through:
     if len(@sqlQuery) = 0 begin -- first time through
         set @sqlQuery = 'select col1, col2, col3 from ' + currentTableName
     end else begin -- second+ time through
         set @sqlQuery = 'union all select col1, col2, col3 from ' + currentTableName
     end
-- after the loop add the create view. Could double check it worked by checking length = 0 again
set @sqlQuery = 'CREATE VIEW myViewName AS ' + @sqlQuery
Once the query string is built up you will execute it with
PREPARE stmt FROM @sqlQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Thanks for the input, but who down rated my question? :) – sean hawk May 23 '12 at 18:07 — sean hawk, May 23 '12 at 18:07

score 2 · Answer 2 · answered May 23 '12 at 17:03

2

If I understand your question correctly UNION is what you need. Something like

SELECT field1, field2
FROM (
SELECT field1, field2 from table1
UNION
SELECT field1, field2 from table2
) all_tables
WHERE all_tables.field1 like "%whatever%

answered May 23 '12 at 17:03

DrewM

1,878
14
13

score 0 · Answer 3 · answered May 23 '12 at 18:41

Assuming they have the same columns or similar:

insert into #table
Select * from (Select * from tbl1 
               Union 
               select * from tbl2 
               Union 
               select * from tbl3)

If they don't have the same number/type of columns then you should provide us with that information.

How to merge multiple tables with similar names in MySQL

3 Answers3