SQL Server: Joining Tables from Different Databases on the Same Server

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

SQL Server Query To Joining tables from different databases
SQL Server Query To Join-Tables From Different Databases

The SQL queries specified in this article is tested on SQL Server 2014.

Related Articles:


4 thoughts on “SQL Server: Joining Tables from Different Databases on the Same Server”

  1. SELECT HRMS.EmpCode FROM [RicohDocs_THF_3.0].AMS.MapEmployeeByLocation AS PMT
    Left JOIN HRMS_UAT.dbo.tbl_intranet_employee_jobDetails AS HRMS ON PMT.EmployeeCode = HRMS.EmpCode

    And Error……

    Msg 468, Level 16, State 9, Line 2
    Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AI” in the equal to operation.

    Reply
    • Hi Kamal,
      The error is due to comparing columns of different collation in the join condition. Try one of the below solutions to fix the error:

      Solution 1:
      Use collate clause to explicitly convert the collation of one of the columns to match the other
      Example: table_1.field collate SQL_Latin1_General_CP1_CI_AS = table_2.field

      Solution 2:
      Using collate database_default in the comparison statement
      Example: table_1.field collate database_default = table_2.field

      Hope this will help you.

      Reply

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.