Insert Into.... Select

tombell

Hi,

I need to run the above query but the problem is that the two databases are on different hosts.
What I mean:
The query : INSERT INTO host1.mydb.mytable SELECT * FROM host2.mydb.mytable.

The two tables are the same in both databases. Well, you could say replicate them, but no I only need particular records

Is there a way this can be done?

Thanks

devinemke

what's the problem exactly?

connect the the first host, run the INSERT query
connect to the second host, run the SELECT query

tombell

Hi,

The problem is that the query mentioned, is one query. If I understood your question correctly, I got the impression that you think these are two different queries.

Thanks

devinemke

you didn't mention what RDBMS you are using but i believe most of them only allow a single query to access a single host. if you intend to use subqueries (which your code seems to suggest) this counts as a single query. however, it appears from your syntax that your subquery could quite easily be written as two separate queries and thus could be easily done accross two separate hosts.

am i still misunderstanding what you intend to do?

tombell

Hi,

I am using mysql 4.0.22
It is one query but not a subquery, check the link:
http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html

The problem is that the query involves two diferrent hosts.

devinemke

again, i do not believe that mySQL can access multiple hosts in a single query (somebody please correct me if i am wrong).

and again, what you are trying to do could quite easily be done with two query (on two hosts).

tombell

That's what I am trying to find out.
If it can access two hosts from one query. I had doubts about that and it seems as though it can't.

Now as for the reason I want to do something like that.
Had the query I want worked, it would save me a lot of trouble, because I want to archive data from one host to another (not replicate). The query mentioned is just one of a series I have to execute for the process to be completed.

Now, I will have to do a select query in the first host and an insert to the other. The problem (not really a problem) is that there are multiple tables involved.

Anyway, as I said it would save me a lot of trouble and time if the query could access two hosts.