How do I join 2 tables from different servers

Question

0.00/5 (No votes)

See more:

I have Table1 in Database1 in Server1 and i want to join a column1 in table1 in database1 in server2 to the Table1 in Database1 in Server1.

What I have tried:

I ran a script as follows to connect the 2 servers

SQL

exec sp_addlinkedserver 'server2'

and then i used this script to join my 2 tables

SQL

SELECT Table1.Column1, [server2]. 
       [database1].dbo.table1.column1
FROM Table1, [server2].[database1].dbo.table1.column1
LEFT OUTER JOIN [server2].[database1].dbo.table1 ON(Table1.Column1 = [server2]. 
                [database1].dbo.table1.column1)
ORDER BY table1.column1

Posted 24-Apr-18 23:53pm

Member 13475897

Add a Solution

Comments

Santosh kumar Pithani 25-Apr-18 6:24am

If you created linked server then use "select t1.col,t2.col2 from [linkedserver1].[db].dbo.table1 AS t1 INNER JOIN [linkedserver2].[db].dbo.table2 AS t2 ON(t1.col1=t2.col1)"

Member 13475897 25-Apr-18 6:39am

Thank you, this sort of worked. The only problem is my table has no data in.Please find my full query below and ask me to write it over in terms of table1, etc. if you need that.

select OEINVH.INVNUMBER,OEINVH.ORDNUMBER,OEINVH.SHINUMBER,OEINVH.CUSTOMER,
OEINVD.LINENUM, ICITEM.ITEMNO,ICIOTH.MANITEMNO[Barcode], OEINVD.QTYSHIPPED,OEINVD.INVUNIT,OEINVD.UNITPRICE,
OEINVD.EXTINVMISC[Line Total], SalesOrderDetail.sord_Name
from [SVWSUSAV].[LOGDAT].dbo.OEINVH AS OEINVH
LEFT OUTER JOIN OEINVD ON(OEINVH.INVUNIQ = OEINVD.INVUNIQ)
LEFT OUTER JOIN ICITEM ON(OEINVD.ITEM = ICITEM.FMTITEMNO)
LEFT OUTER JOIN ICIOTH ON(ICITEM.ITEMNO = ICIOTH.ITEMNO AND ICIOTH.UNIT = OEINVD.INVUNIT)

INNER JOIN [CRM\SQLEXPRESS].[CRM_Test].dbo.SalesOrderDetail AS SalesOrderDetail
ON(OEINVH.CUSTOMER = SalesOrderDetail.sord_Name COLLATE DATABASE_DEFAULT)

WHERE OEINVH.CUSTOMER IN('SHO006','SHO005')
AND OEINVH.INVUNIQ NOT IN(SELECT INVUNIQ FROM InvoiceSent)
AND OEINVH.INVDATE >= 20180415
order by OEINVH.INVNUMBER,OEINVD.LINENUM

Santosh kumar Pithani 25-Apr-18 7:02am

you have used table names multiple times without giving proper aliases,ambiguity issues will occur.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)