SQL Server CROSS JOIN between two Servers

Question

Hi,
I have two SQL Servers. I need to CROSS JOIN two tables one from each SQL Server. How can I do this?

        string sQuery = "SELECT DISTINCT [Operations Sequence Number], LTRIM(RTRIM([Work Center])) AS CellName FROM OPERATIONS_BY_WORKCENTER CROSS JOIN tblWorkCenter WHERE ([Order Number] = '" + cmbWorkOrderNum.Text + "') AND (LTRIM(RTRIM(OPERATIONS_BY_WORKCENTER.[Work Center])) LIKE '%' + tblWorkCenter.WorkCenterName + '%') ORDER BY [Operations Sequence Number]";

So the large Table is OPERATIONS_BY_WORKCENTER on Server1 and the smaller table is tblWorkCenter on Server2

I have no clue how to do this...

Answer 1

Hi，@Alvord, Timothy

Welcome to Microsoft T-SQL Q&A Forum!

Step1：use sp_addlinkedserver---Here is a link about sp_addlinkedserver
It seems to me that you can use sp_addlinkedserver to create server links. See the reference documentation for usage. When you've established the server link, just prepend another server to the database name.

Step2:Modify the code--Prepend the specific server name to your data table name

string sQuery = "SELECT DISTINCT [Operations Sequence Number],LTRIM(RTRIM([Work Center])) AS CellName   
FROM [MyDatabaseOnDB1].[dbo].OPERATIONS_BY_WORKCENTER   
CROSS JOIN [DB2].[MyDatabaseOnDB2].[dbo].tblWorkCenter   
WHERE ([Order Number] = '" + cmbWorkOrderNum.Text + "')   
AND (LTRIM(RTRIM(OPERATIONS_BY_WORKCENTER.[Work Center]))   
LIKE '%' + tblWorkCenter.WorkCenterName + '%')   
ORDER BY [Operations Sequence Number]";  

Step3：Once the link is established, you can also use OPENQUERY to execute SQL statements on the remote server and just pass the data back to you.
Refer to this example:

-- Fetch data from the other database server  
SELECT *  
INTO #myTempTable  
FROM OPENQUERY([DB2], 'SELECT * FROM [MyDatabaseOnDB2].[dbo].[MyOtherTable]')  
  
-- Now I can join my temp table to see the data  
SELECT * FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1  
    INNER JOIN #myTempTable tab2 ON tab1.ID = tab2.ID  

Tip: If your dba does not allow linked servers, you can use OPENROWSET.
Here is another document for remotely linking the database for your reference.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I need to CROSS JOIN two tables one from each SQL Server. How can I do this?

Cross server/database queries are common and works. But why a cross join?
But really, you posted to less on information.
Please post table design as DDL, some sample data as DML statement and the expected result.