SQL Server - How to perform query on two different server and different database

Asked By Bhanu Pratap Singh on 20-Jun-14 06:05 AM
Hi, Robe

I want to perform a query on two table. which is inside two different server and database.
how to do.

table name is tblTest

OPSOFDPendingNDR Idx_AWBNo 2 1 AwbNo
OPSOFDPendingNDR idx_NDRDate 3 1 NDRDate
OPSOFDPendingNDR idx_NDROfficeID 4 1 NDROfficeID
OPSOFDPendingReportData idx_AWBNo 2 1 AWBno

with same row and column are in same table name on different server

I want to match the record. and get find out different records from one table

how to do it

Robbe Morris replied to Bhanu Pratap Singh on 20-Jun-14 08:21 AM
If the servers are not already linked, you'll need to link them with "sp_addlinkedserver".

From there, use the fully qualified object name.  Also use a table alias in your code.  Here's a sample going against a fake development and production database.  I used "d" and "p" as aliases to development and production.

select d.UserID, p.FirstName
  from [developmentservername].[sql server instance name].dbo.[YourTableName] d
LEFT JOIN [productionservername].[sql server instance name].dbo.[YourTableName] p on p.UserID = d.UserID
  where d.UserID = 'blah blah blah'
    and p.FirstName = 'some other value'