Querying data by joining two tables in two database on different servers
Solution 1
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.
Solution 2
Try this:
SELECT tab2.column_name
FROM [DB1.mdf].[dbo].[table_name_1] tab1 INNER JOIN [DB2.mdf].[dbo].[table_name_2] tab2
ON tab1.col_name = tab2.col_name
Solution 3
If a linked server is not allowed by your dba, you can use OPENROWSET. Books Online will provide the syntax you need.
Solution 4
From a practical enterprise perspective, the best practice is to make a mirrored copy of the database table in your database, and then just have a task/proc update it with delta's every hour.
Solution 5
If the database link option is not available, another route you could take is to link the tables via ODBC to something such as MS Access or Crystal reports and do the join there.
Kashif
I'm Kashif, a .NET Developer. Shortcuts C# Questions ASP.NET Questions SQL Questions
Updated on July 08, 2022Comments
-
Kashif almost 2 years
There are two tables in two different databases on different servers, I need to join them so as to make few queries. What options do I have? What should I do?
-
Jhanvi over 11 yearsis it possible with php-mysql ..if yes then can you please suggest me a way how can i grow with that option?
-
Scott Arrington over 11 yearsI have no idea if MySQL supports linked servers. This answer is specific to Microsoft SQL server.
-
PJSCopeland over 9 yearsIf anyone is looking for a PostgreSQL answer, try this: postgresql.org/docs/9.4/static/postgres-fdw.html
-
Wladimir Gramacho almost 5 yearsAnswers should be generic, not just a copy-paste of your use cases. Also, this does not help the user that made the question.
-
Luis H Cabrejo over 4 yearsI guess no one said not to use php... I had the same issue and I was able to solve it with some programming