Joining across databases

Hi there,
Can anyone tell me if its possible to perform a join on two tables that
are resident in different databases - both databases are running on the
same machine and under the same SQL Server 2005 instance. If anyone
could tell me roughly how to do this then I'd be very greatful.
If it is possible, could I also as - is it necessary to define
relationships between the tables in order for the join to work, and is
that even possible?
Many thanks for anyone who can advise
Kindest Regards
SimonYou can reference objects in other databases on the same server by using
3-part names: <database>.<schema>.<object>
SELECT a.Col1, b.Col2
FROM Schema1.Table1 a
JOIN OtherDatabase.Schema2.Table2 b ON
a.Table1Col = b.Table2Col
Cross-database joins are like any other joins; no constrains are required.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Simon
Have you tried?
select <columns> from db1.dbo.table1 t1 join db2.dbo.table1 t1
on t1.col=t2.col
--or
create a view that contains a select statement from requierd database and
join the view with a "source" database
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Many thanks Guys
Simon