Querying Data by Joining Two Tables in Two Database on Different Servers

Querying data by joining two tables in two database on different servers

You'll need to use sp_addlinkedserver to create a server link. See the reference documentation for usage. Once the server link is established, you'll construct the query as normal, just prefixing the database name with the other server. I.E:

-- FROM DB1
SELECT *
FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1
    INNER JOIN [DB2].[MyDatabaseOnDB2].[dbo].[MyOtherTable] tab2
        ON tab1.ID = tab2.ID

Once the link is established, you can also use OPENQUERY to execute a SQL statement on the remote server and transfer only the data back to you. This can be a bit faster, and it will let the remote server optimize your query. If you cache the data in a temporary (or in-memory) table on DB1 in the example above, then you'll be able to query it just like joining a standard table. For example:

-- Fetch data from the other database server
SELECT *
INTO #myTempTable
FROM OPENQUERY([DB2], 'SELECT * FROM [MyDatabaseOnDB2].[dbo].[MyOtherTable]')

-- Now I can join my temp table to see the data
SELECT * FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1
    INNER JOIN #myTempTable tab2 ON tab1.ID = tab2.ID

Check out the documentation for OPENQUERY to see some more examples. The example above is pretty contrived. I would definitely use the first method in this specific example, but the second option using OPENQUERY can save some time and performance if you use the query to filter out some data.

Joining tables from different servers

Without more details, it's hard to give direct examples, but here is the basic idea:

First, outside of the stored procedure, the host server (the server the stored procedure will be on) has to know about the second server, including (possibly) login information.

On your main server, run the sp_addlinkedserver stored procedure. This only has to be done once:

exec sp_addlinkedserver @server='(your second server)';

If you need to provide login information to this second server (for example, the process can't log in with the same credentials that are used in the initial database connection), do so with the sp_addlinkedsrvlogin stored proc:

exec sp_addlinkedsrvlogin @rmtsrvname='(your second server)',
@useself=false, 
@rmtuser='yourusername', 
@rmtpassword='yourpassword';

Then, in your stored procedure, you can specify tables on the second server:

SELECT table1.*
FROM table1
INNER JOIN [secondserver].[database].[schema].[table] AS table2 ON
    table1.joinfield = table2.joinfield

Query tables between two different servers in SQL Server

Another way to query a database hosted in a remote SQL Server is the OPENROWSET T-SQL function. To use the OPENROWSET ad hoc method, you need to provide all connection information that is required to connect to the remote SQL server and many other resources.

Using OPENROWSET requires enabling the Ad Hoc Distributed Queries advanced configuration option same as the OPENDATASOURCE function.

You need to provide the provider’s name, the connection string and the query as follows:

OPENROWSET(‘providername’,’datascource,’query)

Join two tables from different database of different server in laravel

You can do so easily with Eloquent models. It is not proper SQL joins but you can achieve relationship querying across multiple database or event servers.

1. In your config/database.php file, declare as much connection as needed
2. Create the models you want for each table
3. Specify the $connection attribute in the models

For example:

class Model1 extends Model
{
    public $connection = 'mysql_database';
    
    public function model2()
    {
        return $this->belongsTo(Model2::class);
    }
}

class Model2 extends Model
{
    public $connection = 'postgre_database';
    
    public function model1s()
    {
        return $this->hasMany(Model1::class);
    }
}

You can then use the relations between those models as normal Eloquent relationship

Querying data by joining two views in two database on different servers

You can use the federated feature in mysql.

1. Enable Federated in your mysql

Log into the mysql CLI with root (or another account with sufficient privilege).

Type: show engines;
You should see no FEDERATED engine at this point, like this:

mysql> show engines;
+------------+---------+------------------------------------------------------------+---    -----------+------+------------+
| Engine     | Support | Comment                                                    |  Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--- -----------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO               | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO            | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO            | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)

To enable the federate engine, type the following:

install plugin federated soname 'ha_federated.so';

Dont mind if you get ERROR 1125 (HY000): Function 'federated' already exists

You can now safely add the line 'federated' to the /etc/my.cnf file like this:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
federated

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Restart mysqld (service mysqld restart, etc...)

After the restart, go back in to the mysql CLI.

Type 'show engines;'
You should now see the FEDERATED Engine available and with SUPPORT as YES.

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| FEDERATED  | YES     | Federated MySQL storage engine                             | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.00 sec)

Restart mysql

service mysqld status
service mysqld stop
service mysqld start 

2. Create view1 in db1 and view2 in db2

In db1,

CREATE table federated_table (
id int not null,
name   VARCHAR(32) NOT NULL DEFAULT ''
) 
ENGINE=FEDERATED

CONNECTION='mysql://user_name:password@remote_server_name:3306/db2/view2';

Note the federated_table and view2 must same column names and type

3. Execute SELECT * FROM db2.federated_table;

4. Execute select * from db1.view1 union all select * FROM db1.federated_table;

Refer : http://eves4code.blogspot.in/2015/12/querying-data-by-joining-two-views-in.html

---

Related Topics