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.