SQL Cross Server Joins

Question

Is it possible to do cross-server Joins (if the databases are on different servers, e. g. SQL Server and Oracle)? If yes, which database Servers allows cross-database joins?

Is it possible to do cross-server Joins in SQL Server or Oracle? Can we join Oracle database with SQL Server database? How can it be done?

I can't speak for Oracle, but from SQL Server, yes you can query other server's using Linked Servers. Note, however, that cross-**server** queries are not the best performers. — Thom A, Dec 15 '17 at 14:50
Maybe you could connect with two different connections, and interprete the databases with a 3rd party language (i.e. python, php etc...) — mlwn, Dec 15 '17 at 14:50
better way might be to use an utility which fetches data from source server and inserts them on target server — michal.jakubeczy, Dec 15 '17 at 14:51
I'm voting to close this as too broad, but Lamu has the right answer for ad hoc queries which is a linked server (https://blogs.msdn.microsoft.com/psssql/2015/07/31/how-to-get-up-and-running-with-oracle-and-linked-servers/). — Gordon Linoff, Dec 15 '17 at 14:53
Also, note, what you're asking to do here is not a "Cross database" query. A Server will be hosting many databases, and querying 2 of those in the same query is a trivial task (i.e. in SQL Server `SELECT * FROM DB1.dbo.Table1 T1 JOIN DB2.dbo.Table2 T2 ON T1.ID = T2.ID`). What you are asking about are **Cross-Server** queries. — Thom A, Dec 15 '17 at 14:55
Possible duplicate of [SQL statement joining Oracle and MS SQL Server](https://stackoverflow.com/questions/3992573/sql-statement-joining-oracle-and-ms-sql-server) — gh9, Dec 15 '17 at 14:56

score 2 · Answer 1 · answered Dec 15 '17 at 15:02

it is possible that you can join multiple tables on Different Databases on the Same server or different server in MS SQL Server.

If both databases are on the same server you can just join like this

SELECT
*
FROM [Database1].[Schema].[TableA] A
JOIN [Database2].[Schema].[TableB] B
ON A.Key = B.Key

If they are on different servers or different SQL instances on the same server machine, You can either use a Linked Server or a Bridge Query(OPENDATASOURCE).

To use Linked Server, first, you need to add a linked server connection

Refer this Article to know how to add the linked server.

Once you have configured Linked server you can Query the Tables like this

SELECT
*
FROM [ServerName1].[Database1].[Schema].[TableA] A
JOIN [ServerName2].[Database2].[Schema].[TableB] B
ON A.Key = B.Key

or you can use the opendatasource

SELECT
    * FROM TableA A
JOIN OPENDATASOURCE (
   'SQLNCLI', -- or SQLNCLI
   'Data Source=OtherServer\InstanceName;Catalog=RemoteDB;User ID=SQLLogin;Password=Secret;').RemoteDB.dbo.SomeTable B
ON A.KEY = B.KEY

score 1 · Answer 2 · answered Dec 15 '17 at 14:55

Apparently you can,

STackover post

Yes- both Oracle and SQL Server support the linked server concept. That allows you to reference the other server using a 4 part name. For example:

select * from LocalDb.Schema.Table cross join OracleLinkedServer.RemoteDb.RemoteSchema.RemoteTable

As a side note I googled cross-database Joins in SQL Server or Oracle and the first result was the duplicate stack overflow post. Please attempt to answer the question yourself before posting

SQL Cross Server Joins

2 Answers2