How to Create a join?

Question

Is this possible:

1. Joining tables in two databases in one server?

   1.1 What connection string shall i use?

2. Joining tables in two databases in different server?

   2.1 What connection string shall i use?

Answer 1

I you are using sql server

than use OpEnQuery or OPENROWSET will resolved your issue.

step 1:

EXEC sp_addlinkedserver
    @server = 'TEST',
    @srvproduct = 'SQLServer OLEDB Provider',
    @provider = 'SQLOLEDB',
    @datasrc = 'InfoNet'

step 2 : For Example :

SELECT loc.OrderID, loc.ProductID, rmt.ProductName
FROM [Order Details] loc INNER JOIN 
OPENQUERY(InfoNet, 'SELECT * FROM Northwind.dbo.Products') rmt
    ON loc.ProductID = rmt.ProductID

Answer 2

If I remember correctly you can do that in the follwoing fashion:

select * from [database name].[owner].table_name a
inner join [database_name].[owner].table_name b on (a.id = b.a_id)

If the database is on another server you must first create a linked server:

USE master 
GO
EXEC sp_addlinkedserver 
'RemotDB',
N'SQL Server'
GO

When servers are linked you can reference a table in on the other server with the following syntax:

[server_name].[database_name].[owner].table_name

Answer 3

Not sure what OpenQuery is, but I've done both scenarios without it. Just use fully qualified names.

1:

SELECT * 
FROM SpecificDatabase.dbo.SpecificTable spec
INNER JOIN CommonDatabase.dbo.CommonTable comm ON spec.someField = comm.someField

2: (setup a linked server object)

SELECT * 
FROM SpecificDatabase.dbo.SpecificTable spec
INNER JOIN LinkedServer.CommonDatabase.dbo.CommonTable comm ON spec.someField = comm.someField

For connection strings, are you just referring to what goes in the From and Join clauses or do you mean connection string values in the code?

Answer 4

Nobody has answered your question regarding the connection string, so I'll jump in here. If you have 2 servers A and B, you can set up a linked server on server A pointing to server B (as detailed in the other postings). You then run your query on server A referencing server B in your query (again as detailed in the other postings). You are running this query on server A, so your connection string will be to server A.

Make sure the user in the connection string has authorisation to access the tables in the query on both servers. If not, you can set up the linked server to always use a specific set of credentials by using the sp_addlinkedsrvlogin system stored procedure. See http://msdn.microsoft.com/en-us/library/ms189811.aspx for further details.