CODEWITHSUNDEEP
.netsqlsql-serversql-server-2005t-sql
Smur
Smur

Reputation: 3113

Query two different Servers

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?

Upvotes: 4

Views: 505

Answers (2)

John Hartsock
John Hartsock

Reputation: 86872

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

Upvotes: 4

Germ
Germ

Reputation: 6540

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

SELECT * FROM SERVER.DATABASE.SCHEMA.TABLE

Upvotes: 1

Related Questions