Is there any way to use the join operation for two tables from different databases?
Both databases are on different server.
Asked
Active
Viewed 832 times
0
-
http://stackoverflow.com/questions/11426929/can-we-use-join-for-two-different-database-tables – Lucky Mar 06 '14 at 06:59
-
Databases are on different server. – user3317777 Mar 06 '14 at 07:01
-
http://www.mssqltips.com/sqlservertip/2855/sql-server-multi-database-query-with-registered-servers/ – Lucky Mar 06 '14 at 07:03
1 Answers
0
If both databases are MySQL, then FEDERATED ENGINE provides functionality to join MySQL databases in different servers. And read the limitations too.
Wikipedia: The MySQL Federated storage engine for the MySQL relational database management system is a storage engine which allows a user to create a table that is a local representation of a foreign (remote) table. It utilizes the MySQL client library API as a data transport, treating the remote data source the same way other storage engines treat local data sources whether they be MYD files (MyISAM), memory (Cluster, Heap), or tablespace (InnoDB). Each Federated table that is defined there is one .frm (data definition file containing information such as the URL of the data source). The actual data can exist on a local or remote MySQL instance.
To create a Federated table, one has to specify a URL in the "CONNECTION" string:
create table t1 (
a int,
b varchar(32))
ENGINE=FEDERATED CONNECTION='mysql://user@hostname/test/t1'
The connection URL is in the format of:
scheme://user:pass@host:port/schema/tablename
Raging Bull
- 18,593
- 13
- 50
- 55