Today I came across a requirement where I had to write a SQL query joining tables from different databases on the same SQL Server. I thought of sharing the simple concept with you.
For querying multiple tables in different databases on the same server, all we have to do is use the fully qualified table name. The only condition is, the user logged into the query analyzer (or used for executing the query) should have permission on both the databases.
For example, I have two databases MtbDatabaseOne and MtbDatabaseTwo in the SQL Server. MtbDatbaseOne has a table named TableOne. MtbDatabaseTwo has a table named TableTwo.
Joining these two tables in a select query is very simple. The select query will be similar to the one we normally use to select data from multiple tables in the same database. The only difference is instead of just mentioning the table name, we have to specify the fully qualified table name. The fully qualified table name looks like [Database Name].[database owner name].[table name].
In my example the fully qualified table name for TableOne is [MtbDatabaseOne].[dbo].[TableOne] and TableTwo is [MtbDatabaseTwo].[dbo].[TableTwo]
So the select query looks like this:
select t1.ID, t1.Name, t2.Name
from [MtbDatabaseOne].[dbo].[TableOne] as t1
inner join [MtbDatabaseTwo].[dbo].[TableTwo] as t2 on t1.ID = t2.ID
The SQL queries specified in this article is tested on SQL Server 2014.