Joining Tables from Different Servers

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

Joining tables from 2 different servers through linked server - SQL Server

You dont need to specify the local server on your query cause it's not a linked server. So change your query to:

SELECT *
FROM [db1].[dbo].[Order] T1
JOIN [S2].[db1].dbo.[Invoice] T2
ON T1.[OrderID] = T2.[InvoiceID]

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

MYSQL Joining Tables from Databases on Different Servers

You can use a FEDERATED table. There are no other options as far as I know.

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.

---

Related Topics