0

Consider that I have 2 databases with the exact same structure sitting on the same server one with data and the other is empty. The usual way to copy the data from the first one to the second is to export it on my local machine and then import it on the other one but what if the database size is over the server's limit (and I have no control over that limit), the only two options I know of are:

  • To export bits and pieces of the first database over a number of times so I'd end up with 10 sql files for example each not exceeding the upload size limit and import those one by one.
  • To write a query to copy a table from the first database to the second (i.e. INSERT INTO database2.table SELECT * FROM database1.table) Which is a better option if you have small number of tables because you'd have to write the same query for each table.

Is there a way to use the second method where I wouldn't have to write the tables' names? In theory I should be able to get the tables names from the information_schema database and work on those, but I'm not sure where they are stored and how that would work in a query.

1
  • use show tables to get the list of tables in your first database. write a script to loop through the table names and create your insert queries to same table in your second database.
    – Masoud
    Jun 2, 2015 at 6:32

1 Answer 1

1

You can query information schema for list of all tables in your source database and build the import commands.

SELECT
    CONCAT('INSERT INTO target_db.', TABLE_NAME, ' SELECT * FROM source_db.', TABLE_NAME, ';') restore_command
FROM information_schema.TABLES
WHERE
    TABLE_SCHEMA='source_db'
    AND TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_NAME;

Then copy/paste the output in query window.

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.