Query two different Servers

Question

I have to query two different servers from a dynamically built query.

It basically gets data from one server, treats it, and inserts it into another server.

The only problem is I have to be sure it works for both situations: If both the source and destination databases are on the same server, and if they're not.

I understand the concept of using Linked Servers in SQL Server, but I cannot think of a way to consider both alternatives, same server and different servers.

A little help?

Answer 1

Use a fully qualified table name for both tables (local and remote)

SELECT * FROM SERVER.DATABASE.SCHEMA.TABLE

Answer 2

Two linked servers are not necessary... just one per server. Example

PhysicalServerA
   SQLServerA
      DatabaseA
      DatabaseB
   LinkedSQLServerB  // A linked server to SQL Server B

PhysicalServerB
   SQLServerB
      DatabaseC
      DatabaseD
   LinkedSQLServerA  // A linked Server to SQL Server A

Now Server A can have queries to itself like:

SELECT * FROM SQLServerA.DatabaseA.dbo.TableName

And queries to LinkedSQLServerB like

SELECT * FROM SQLServerB.DatabaseC.dbo.TableName

Now Server B can have queries to itself like:

SELECT * FROM SQLServerB.DatabaseC.dbo.TableName

And queries to LinkedSQLServerA like

SELECT * FROM SQLServerA.DatabaseA.dbo.TableName