Join Tables in 2 SQL Server Databases

31 Oct 2006 7:27 AM

TigerMan

Hi,

Is there any way I could do a join across 2 tables in 2 separate databases?
I want to be able to set the connection string on both databases in vb.net
then do a join query in code on the tables in the databases

TIA

31 Oct 2006 1:46 PM

smarty

You only need to specify database for each table like this:
SELECT * FROM Northwind..Employees

SELECT * FROM
db1..table1 T1 INNER JOIN db2..table2 T2
ON T1.id = T2.test1

For more, you can search in SQL Server Book Online with the keyword:
"Choosing a Database"

31 Oct 2006 9:50 PM

TigerMan

Thanks to you both for the help

I did this to get it working:

EXEC sp_addlinkedserver
@server = 'TEST1',
@srvproduct = 'SQLServer',
@provider = 'SQLOLEDB',
@datasrc = 'TEST\INSTANCE'
EXEC sp_addlinkedsrvlogin 'Test1', 'false', 'user', 'user', 'pwd'

I then ran this after to get the query:

SELECT * FROM Test1.DB1.dbo.Table1 T1
INNER JOIN Test1.DB2.dbo.Table1 T2
ON T1.ID = T2.ID

Show quote

"smarty" <bthtin5***@yahoo.com> wrote in message
news:1162302408.499681.3240@m73g2000cwd.googlegroups.com...
> You only need to specify database for each table like this:
> SELECT * FROM Northwind..Employees
>
> SELECT * FROM
> db1..table1 T1 INNER JOIN db2..table2 T2
> ON T1.id = T2.test1
>
> For more, you can search in SQL Server Book Online with the keyword:
> "Choosing a Database"
>

31 Oct 2006 1:49 PM

Cowboy (Gregory A. Beamer)

You can take two tables and then add a relationship. As long as you only
gather related data, you are golden.

If this is not good enough, you are better to link the servers in SQL Server
and do the query from one server or the other, using the link to bridge the
servers for the join.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************************************************
Think outside of the box!
*************************************************

Show quote

"TigerMan" <nospam@antispam.com> wrote in message
news:eqVJ%233L$GHA.4356@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> Is there any way I could do a join across 2 tables in 2 separate
> databases? I want to be able to set the connection string on both
> databases in vb.net then do a join query in code on the tables in the
> databases
>
> TIA
>