joining tables from 2 different databases

[dynamorevy]

Hi - please could someone point me in the direction of a good tutorial for
joining tables from 2 different databases or list some basic sql to get me
started.....

 

[fxthoth]

Hi,
Here is example:

SELECT *
FROM [honza\sqlexpress].[AdventureWorks2008].[HumanResources].[Department]

means:
[honza\sqlexpress] = computer with SQL Server where you want connect
[AdventureWorks2008] = name of database
[HumanResources] = name of schema
[Department] = name of table

Server name and database name are optional, but in your case, you have to use database name. So inner join may looks like:

SELECT AW2008.name, AW2005.name
  FROM [AdventureWorks2008].[HumanResources].[Department] AS AW2008
  INNER JOIN [AdventureWorks2005].[HumanResources].[Department] AS AW2005
  ON AW2008.DepartmentID=AW2005.DepartmentID

In this SELECT, you will connect to DB AdventureWorks2008 and AdventureWorks2005. And you will join tables Department on the column DepartmentID.

 

[SQLSister]

And if the databases happen to be on differnt servers, you will need to set up linked servers first. If you need this, look up how to do it in BOL.

"NOTHING is more important in a database than integrity." ESquared