How to select a table from one machine to another using sql server 2008 r2?

Question

I have explained the scenario below:

I am having two servers:

1. server 1
2. server 2

In both the servers I have Sql Server 2008 r2.

In server 1, I have a database named "DB_Server1" and in server 2, a database named "DB_Server2".

In DB_server1 database, I have a table named "TB_Server1" and in DB_Server2, a table named "TB_Server2".

My requirement is, in DB_Server1, I'm going to write a stored procedure which selects the table "TB_Server2" from DB_Server2 which is located in the server 2.

How can I achieve the above requirement?

Answer 1

you can use linkserver

at first select Server1

step 1: exec sp_addlinkedserver 'Server2'

step 2: select * from [Server2].[DB_Server2].TB_Server2

Answer 2

Use Linked Server:

Allows you to query databases and tables on remote servers as though they are part of the local database. Very easy to setup (just call exec sp_addlinkedserver) and once defined uses nothing but plain old SQL.

Here is a simple tutorial about how to create a linked server. After creating linked server, we can query it as follows:

select * from LinkedServerName.DatabaseName.dbo.TableName

Click here for another tutorial. Read more about Linked Servers here.

Answer 3

You can create linked server (http://msdn.microsoft.com/library/ff772782.aspx)