Joining 2 SQL Servers

Question

I am trying to search 2 servers (A and B). Server A has database PROD_A and Server B has PROD_B. PROD_A has table1 and PROB_B has table2. How can I merge 2 tables from 2 different SQL Servers? Already done setting up link servers but I am having issues with query syntax. Thanks.

possible duplicate of [How to create linked server MySQL](http://stackoverflow.com/questions/5370970/how-to-create-linked-server-mysql) — Makoto, May 18 '13 at 16:39

Gordon Linoff · Answer 1 · 2013-05-18T16:51:09.573

1

You need to create a linked server, using sp_addlinkedserver (documented here). On Server A issue the command:

sp_addlinkedserver ServerB

Then access the remote table using:

select *
from ServerB.Prod_B.dbo.table2

This is the four-part naming convention for remote tables. It assumes the remote table is in the schema called "dbo". If not, change that to the right schema.

If you have permissions problems, then post another question.

If you want to access them in one query, the put the tables in one query

select *
from ServerB.Prod_B.dbo.table2 join
     Prod_A..table1
     on . . .

edited May 18 '13 at 16:51

answered May 18 '13 at 16:44

Gordon Linoff

1,242,037
58
646
786

His problem isn't accessing the foreign tables, it's in accessing two tables in one query. – DougM May 18 '13 at 16:49
Thank you. The link server has been added already. Under Server A's server object's linked servers but during query it's still not found. Do you any exact syntax for it's query? Thank you. – Rose May 18 '13 at 16:51
@user2397263 . . . Amend your question to show the query that you are trying to run. – Gordon Linoff May 18 '13 at 16:53
Im trying to get column 1 of ServerA.PROD_A.table1 and the column2 of ServerB.PROD_B.table2. Im done with setting up sp_addlinkedserver – Rose May 18 '13 at 17:04
@user2397263 . . . Do you know how to write the query that you want without a linked server? If not, ask another question, leaving out the linked server piece. Show some sample data and the results that you want. – Gordon Linoff May 18 '13 at 18:59
What I did was to specify the linked Server objects during query. It worked. – Rose May 19 '13 at 04:34

score 1 · Answer 2 · edited May 18 '13 at 17:06

1

You probably want a UNION query.

SELECT * FROM [serverA].[dbName].[table1]
UNION
SELECT * FROM [serverB].[dbName].[table2]

As Gordon noted, if it's SQL Server you'll want to refer to each table as its full four-part name.

edited May 18 '13 at 17:06

marc_s

732,580
175
1,330
1,459

answered May 18 '13 at 16:48

DougM

2,808
17
14

. . I assume it's SQL Server because of the statement "SQL Servers" in the question. – Gordon Linoff May 18 '13 at 16:52

score 0 · Answer 3 · answered May 18 '13 at 16:38

0

The feature you're looking for is MUltiServer queries, I used it a few years ago. Take a look at this article.

answered May 18 '13 at 16:38

gpicchiarelli

454
6
16

Im trying to get column 1 of ServerA.PROD_A.table1 and the column2 of ServerB.PROD_B.table2. Im done with setting up sp_addlinkedserver – Rose May 18 '13 at 17:17

Joining 2 SQL Servers

3 Answers3