Querying multiple SQL Server databases with a single SQL query

Today's blog post is about SQL Server basics. In smaller application, you usually have one single database for all your data, and that's fine. However, once your applications start to grow, or, you need to implement some kind of data integration between different databases, you suddenly might need to access two databases.

What if you simply want to query some data from one database, and some other data from another database. Can you do this in a single SQL query? Yes, you can!

Here's how to do this. Firstly, you need to learn that in SQL Server, objects are named with the format "database.schema.object", as in "Northwind.dbo.Customers". Usually, both the database name and the schema have their default values based on your login or the use of the "USE" statement, so most often, you're simply querying data based on the table or view name, as in "SELECT * FROM Customers".

But, since you can also use a fully qualified name, you can execute queries from two databases like this:

SELECT *
FROM MyDatabase1.dbo.SomeTable t, MyDatabase2.dbo.AnotherTable a
WHERE a.ParentId = v.MasterId

With a query like this, SQL Server will happily query these two databases, provided that they are both accessible and working on the same SQL Server instance.

Hope this helps!