CODEWITHSUNDEEP
sql-server
Perry
Perry

Reputation: 1337

How can I get data from 2 different SQL Servers

I have the following situation. I am working with 2 separate SQL servers. Server A hosts the company HR data. There is a view on Server a that provides supervisor info for each employee. I need to get the next supervisor info going up the chain. So I used this to code, I got from the DB admin, to accomplish that

SELECT *
FROM [lawdata].[dbo].[All_Users] ru1 
left outer join [lawdata].[dbo].[All_Users] ru2 on ru1.SUPER_EID = ru2.EMP_EID

Now I have data on a separate SQL Server, Server B, that contains some report data the ReportData table contains the employee ID which matches employee ID numbers shown in the view above from Server A. The questions is how can I merge the view from Server A and the Employee ID on Server B so I can link the supervisors to the data rows on Server B.

I have seen this post but just cannot get the syntax right to make it work with my situation

Thanks

Upvotes: 0

Views: 120

Answers (4)

Perry
Perry

Reputation: 1337

Just in case someone else is trying to solve this same problem here is the solution I came up with; thanks to the suggestion given above

select rd.*, ru1.emp_first, ru1.emp_last, ru1.Super_Last as FirstLineLast,
Super_first as FirstLineFirst,
ru2.Super_Last as SecondLineLast,
2.Super_first as SecondLineFirst
from [TaserEvidence].[dbo].[ReportData] rd left outer join  
[soops-lawrept].[lawdata].[dbo].[My_View] ru1 on rd.OwnerBadgeId = ru1.emp_EID
left outer join 
[soops-lawrept].[lawdata].[dbo].[rob_users] ru2 on ru1.super_EID = ru2.EMP_EID

Upvotes: 0

HardCode
HardCode

Reputation: 6756

For this, I'd create an SSIS package to pull down the data from the lawdata server into the database on Server B once a night - probably just a truncate and reload. This way, all of your queries with lawdata data on Server B is localized to one database on one server.

Upvotes: 1

Steven
Steven

Reputation: 119

it looks like in your code you did a left outer join on something with itself. Try

SELECT *
FROM [server1].[dbname].[dbo].[tablename] A 
left outer join [server2].[dbname].[dbo].[tablename] B on A.columnname = B.columnname
where ["insert where clause here"]

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

You need linked servers. then use 

[ServerName].[DatabaseName].[dbo].[tableName]

Create Linked Servers (SQL Server Database Engine)

Upvotes: 3

Related Questions