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.
Related Articles:
- Read about Joining tables from different SQL Servers.
- Read about the simplest way to update some values from one table to another table.
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.
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.
Getting table name invalid
Hi Veeresh,
Please see if the logged in user has at least select permission for both the databases.