mysql - Copy all information from one table and insert it into another table on different server using PHP
974
I want to copy everything from a table on my local server and insert it into a table on a remote server.
Something like
INSERT INTO table2
SELECT * FROM table1;
How can I adapt this for 2 tables on different servers and databases?
Answer
Solution:
Well, if you want to use PHP, then you could do something like querying everything from one table, simply like SELECT * FROM table, and then iterating over your table with a while loop, inserting one record at a time to the new table. I assume you know some PHP, for this answer to help you. You can't do it with one SQL statement atleast, that's for sure.
Answer
Solution:
Please look at this StackOverflow thread: SQL Insert into … values ( SELECT … FROM … ). There are discussed some compatibility issues across various database engines, too. It should answer your question quite good as long as it is within a single database.
For copies between different database instances have look at backup & restore, export & import mechanisms or at seperate copy scripts in php, python, etc. using either native or ODBC database drivers.