SELECT on 2 instances

I would like to create a table from database db_x (server X) into database db_y (server Y):

CREATE TABLE db_y.new
SELECT u.*, v.*
FROM db_x.u u
INNER JOIN db_x.v v = u.id = v.id
WHERE b < 1
;

If both databases are on the same server this wouldn't be any problem. My problem is that the databases are in different Server. It reports:
"SQL Error (1049): Unknown database 'db_y'".
Is there a way to solve this problem?
Thanks for help.

This has nothing to do with HeidiSQL. As far as I know, only some major DBMS like Oracle support linked servers (and it needs specific server-side configuration anyway). MySQL doesn't.

Thanks for the response. I thought that if it is possible to copy with HeidiSQL from one instance to another:
http://www.heidisql.com/forum.php?t=12301
it should be possible also submitting queries over instances. Obviously I'm wrong.

create table in 1 instance and then export to another. Or export query results directly

Exporting is a clearly defined feature that's used through a GUI. HeidiSQL composes its own SQL queries; it reads from source server and writes to target (which doesn't even need to be a MySQL server at all). Both operations happen consecutively, not at the same time.

What you're asking for is to launch a single query against two different servers. Parsing and executing SQL is entirely the server's responsibility—that was precisely a key feature of SQL when it was proposed some decades ago: you no longer had to say how to retrieve data as in past database systems, on what data you want.

Having a client-side engine that's able to execute arbitrary SQL against different servers would require to:

1. Extend the syntax of the SQL language so you can qualify identifiers with the server name (and servers don't really have names, you'd have to provide either the full credentials or HeidiSQL's stored connection name).

2. Implement a 100% reliable full-fledged SQL parser (current HeidiSQL parser is based on regular expressions thus doesn't always work as expected).

3. Possibly retrieve complete all the rows from the involved tables before being able to filter out unwanted ones (thus making everything terribly slow).

In other words, replicate the functionality of MySQL Server without the benefit of fast local storage.

Said that, the precise query you need can easily replaced with two queries as giordano2 and imre explain.

It is possible in principle to redirect queries to a different mysql server using the FEDERATED storage engine; however, it has several restrictions, and I have never seen an instance of it in practical use. Experimentation would be required.

You're right. I always forget that there's MySQL beyond MyISAM and InnoDB. Sorry if I mislead someone.

Thanks for this info. There is also CONNECT in MariaDB:
https://mariadb.com/kb/en/federated-storage-engine/
.