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?
Asked
Active
Viewed 83 times
0
-
1Easy method is to Use export/import data in PhpMyAdmin. – Riq May 27 '14 at 19:44
-
why would you want to use php for this? – eis May 27 '14 at 19:44
-
Is this a one time operation or something that must be done repeatedly? Is it possible for you to use [FEDERATED tables](http://dev.mysql.com/doc/refman/5.0/en/federated-use.html)? – Bacon Bits May 27 '14 at 19:52
-
I need to update very quickly (at least once per minute) and I don't think that is feasible with export/import. Edit: tried using Federated tables, but it requires static IP's, I think. – user3642365 May 27 '14 at 19:52
-
Sounds like you should explain your use case a little better. copying a whole table every minute seems rather unnecessary in 95% of the cases. Your php code will have to be bootstrapped too by a cron job. In short: Connect to db 1, read everything in memory. Disconnect and connect to DB1, insert into table 2 – Gregory May 27 '14 at 20:00
2 Answers
0
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.
Lozzano
- 582
- 1
- 5
- 12
-
Something like phpmyadmin would be easier if you don't know how to export your tables in MySQL. @Lozzano I know you're just answering the question, I'm not trying to give you a hard time, just hoping OP would reconsider. – dcclassics May 27 '14 at 20:07
0
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.
