Copy many tables in MySQL

Question

I want to copy many tables with similar names but different prefixes. I want the tables with the wp_ prefix to go into their corresponding tables with the shop_ prefix.

In other words, I want to do something like this:

insert into shop_wpsc_*
select * from wp_wpsc_*

How would you do this?

Answer 1

SQL doesn't allow wildcarding table names - the only way to do this is to loop through a list of tables (via the ANSI INFORMATION_SCHEMA/INFORMATION_SCHEMAS) while using dynamic SQL.

Dynamic SQL is different for every database vendor...

Update

MySQL? Why didn't you say so in the first place...

MySQL's dynamic SQL is called "Prepared Statements" - this is my fav link for it besides the documentation. There're numerous questions on SO about operations on all the tables in a MySQL database - just need to tweak the WHERE clause to get the table names you want.

You'll want to do this from within a MySQL stored procedure...

Answer 2

First I would select all tables from the catalog views (the name of those may depend on your dmbs, though if they are ansi compatible they should support INFORMATION_SCHEMA) that start with wp_wpsc_.

(For instance for DB2:

SELECT NAME FROM TABLES WHERE NAME LIKE 'wp_wpsc_%'

)

Then iterate through that result set, and create a dynamic statement in the form you have given to read from the current table and insert into the corresponding new one.

Answer 3

Expanding on OMG Ponies' answer a bit, you can use the data dictionary and write a SQL to write the SQL's. For example, in Oracle, you could do something like this:

SELECT 'insert into shop_wpsc_' || SUBSTR(table_name,9) || ' select * from ' || table_name || ';'
FROM all_tables
WHERE table_name LIKE 'WP_SPSC%'

This will generate a series of SQL statements you can run as a single script. Like OMG Ponies' pointed out though, the syntax will vary depending on what DB vendor you are using (e.g. all_tables is Oracle specific).

Answer 4

You can do this by combining multiple statements into a single prepared statement -- try doing this:

SELECT @sql_text := GROUP_CONCAT(
                              CONCAT('insert into shop_wpsc_',
                                      SUBSTRING(table_name, 9),
                                      ' select * from ', table_name, ';'), ' ')
  FROM INFORMATION_SCHEMA.TABLES
 WHERE table_schema = 'example'
   AND table_name LIKE 'wp_wpsc_%';

PREPARE stmt FROM @sql_text;

EXECUTE stmt;