How to join two tables from two different databases from the same server

MJC22

Member
Joined
Apr 6, 2013
Messages
6
Programming Experience
Beginner
Dear all,

I am relatively new to vb.net/SQL Server and am struggling to find a way of executing a select statement which will allow me to access tables located in different databases. I am under the impression that a connection string can only be referenced to a single database at any one time. The code I typically use to read from a table only allows for one connection string and is as follows:

VB.NET:
            'SQLCon -> Connection to Database containing both tables.
            Dim iSQL As String = "SELECT Table_A.My_Id, Table_A.MyValue, Table_B.MyValue FROM Table_A INNER JOIN Table_B ON Table_A.My_Id = Table_B.My_Id"
            If SQLCon.State <> ConnectionState.Open Then SQLCon.Open()
            SQLCmd = New SqlCommand(iSQL, SQLCon)
 
            Dim R As SqlDataReader = SQLCmd.ExecuteReader
            While R.Read
                    'Do Something...
            End While

How would I achieve the above if Table_A was to reside in Database_A and Table_B was to reside in Database_B?

Thank you in advance for any suggestions.
MJC
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,349
Location
Sydney, Australia
Programming Experience
10+
I've never done it myself but I think that all you need to do is qualify the tables appropriately. In your connection string, don't specify am Initial Catalog so that you connect to the master database by default. You can then simply qualify each table with its database and schema, e.g.
VB.NET:
SELECT *
FROM Database1.dbo.Table1 t1 INNER JOIN Database2.dbo.Table2 t2
ON t1.ID = t2.ID
Like I said, that's untested but I believe it should work.
 

Herman

Well-known member
Joined
Oct 18, 2011
Messages
882
Location
Montreal, QC, CA
Programming Experience
10+
You don't actually have to change your connection string to switch DB context, just stick a "USE master;" in front of your query. It's not even necessary, as long as your user has permissions to access both databases you can just qualify the table names as shown above. If you need to join databases on multiple servers, then you need to create a connection to the secondary server in your SQL Server instance, as a "Linked server". Then you again just qualify your table names, this time using the linked server name as well.
 
Top Bottom