Cross Database Joins

Posted: July 7, 2008 in Uncategorized
Tags: , ,

An old friend of mine today asked me a question that I thought I should go ahead and post.  The question was; how do you run a join across databases.  Do let’s say for example you would like results to be a combination of sales from the Employee database and the Sales database (so you want to know who the best sales person is), but they are in different databases.

Good question but depends on the version of SQL Server.  All you have to do is hit it by the fully qualified name.   So for an example.  If you wanted to do this and both databases (Employee and Test1) are on the same server then you would do a join like this.

Select (Columns)

From Employees.dbo.Employees e

                Join Sale.dbo.sales s on e.id =s.id

So really the answer is no matter what the version you have to use a fully qualified name.  Here is the name pattern <LinkedServerName>.<DatabaseName>.<Owner>.<TABLE> now this is for SQL Server 2000 and older. 

 If you are working with SQL Server 2005 or newer then it changes to <LinkedServerName>.<DatabaseName>.<Scheme>.<TABLE>

There really is not a big difference here.  Just remember if you normally when you write a SQL Statement the defaults of the database you are in and DBO as the owner are used.  If you use one to the left of the Table name such as database name like this (Employees..Employees) I think it looks and reads the best if you still include the owner or the scheme. 

Comments
  1. b00mhauer says:

    Since MS Access seems to be good at doing this across just about any type of linked tables on remote servers, I’m curious how it does this. And, I’ve never seen another tool that does it quite like Access… unfortunately ;/

    • Chris Shaw says:

      Hi Boomhauer,

      Thanks for the comment. Access is a way to handle this for sure. A lot of this is going to depend on what tools that someone is familiar with and are the most comfortable with. Thanks again for the comment, I think I may update this with some additional information including your input.

      –Chris

  2. b00mhauer says:

    Thanks Chris. yeah I’ve researched this a few times over the years, and really I’ve never seen anything else that can take… I guess any ODBC connection… and allow linking tables inside it, AND allow doing join queries acorss those tables. Thus Access can join an oracle table with a sql server one, or even a local MDB/access table. In fact this feature I see as the one reason I still use Access to this day for data hacking projects.
    I’m actually curious where this capability comes from – is it part of the Jet engine, or is it proprietary “Access”, or…? And if one were so inclined to build something similar, let’s say as a c# project, how would one do this… It’s more a mental exercise than anything, but I’m just really curious😉

  3. Kaushik says:

    does this work with DB2?

  4. Alan says:

    Make sure the Collating sequences in the respective databases are compatible

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s