Move Data From One Db To Another

[thomas7263]

I am not sure if this is a pure mysql question or a sqlyog question. I use the query below to move data from one database to another on the same host/connection. My question is – how do I move data from a database in one host to a database in another host?

INSERT INTO database2.table2 (field1,field2,field3) SELECT field1,field2,field3 FROM database2.table2 where etc…;

Thank you for your help

[CalEvans]

thomas7263 wrote on Feb 20 2006, 07:47 PM:

I am not sure if this is a pure mysql question or a sqlyog question.  I use the query below to move data from one database  to another on the same host/connection.  My question is – how do I move data from a database  in one host to a database in another host?

INSERT INTO database2.table2 (field1,field2,field3) SELECT field1,field2,field3 FROM database2.table2 where etc…;

Thank you for your help

[post=”8815″]<{POST_SNAPBACK}>[/post]

Hi,

I do not believe that is possible with either SQLYog ot MySQL (Although I may be wrong). You would need to:

1) Expode the data using SQL and then import it into the second host

2) Write something in PERL or PHP to do the import

3) I believe SQLYog has a job manager that can handle this on an automate basis.

It would be nice if MySQL supported host.database.table.field notation. 🙂

=C=

[peterlaursen]

You can do INSERT.. SELECT only provided that the databases are on the same server/host.

You can use SQLyog 'copy (source) table to other host', then do the INSERTS into target and then drop the copy of the source if databases are on different servers. But that of course could take its time with big databases.

With SQLyog Enterprise DATA synchronization you can sync (what includes inserts) between hosts and you can filter rows to sync using the clause. But it only lets you sync complete rows – not individual columns or the rows that have been 'filtered-in'. refer to: http://www.webyog.com/articles/Using_SQLyo…L_Databases.pdf

NB: edited. garbage that I wrote in the first place!

[peterlaursen]

I got an idea to effectivize it …

1) First create a table with a subset of the data you'll need on the one host

CREATE TABLE smalltable SELECT … WHERE …

2) Now you can copy smalltable to the other host with SQLyog

3) And do as you used to ..

INSERT INTO … () SELECT FROM smalltable WHERE

4) And drop smalltable on both hosts

You cannot automate it with pure SQL.

[Author]

Posts