Join Tables in 2 SQL Server Databases

  • Thread starter Thread starter TigerMan
  • Start date Start date
T

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
 
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"
 
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!
*************************************************
 
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
 
Back
Top