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

[MJC22]

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:

            '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]

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.

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]

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.