Concatenate tables with same name from different servers - CR2013

[Former Member]

Hello,

I need some help.  I need data from 2 tables with the same name on different SQL servers thru an ODBC connection. When I try to get the data by adding both to my datasource I get data from one or the other.  I have considered creating a view, but not sure how to handle this.  Thanks.

[Former Member · Accepted solution]

Hi,

Linked Servers are pretty simple and straightforward, just do a search for the procedure.

I do have an additional comment, though.  Depending on what tables you're gathering, I've found noticeable differences in performance when I use a connector to a DB on Server Instance "A" linked to "B" versus Server Instance B linked to A.  I haven't determined the circumstances affecting one over the other.

If I'm not happy with speed using a connector A, I try switching to B.  I like to use as much SQL code as possible, but you can't get around the connector still being a factor.

Matt

[Former Member · Accepted solution]

Thanks Matt.  That's good to know.

[JWiseman]

hi Dru,

have you looked into creating a Linked Server system?

after setting that up you can create a Command object off of the main connection (a Command object allows for free hand sql as opposed to adding tables from your db) and then use transact sql in the Command for a union query between the two tables.

in the link above there is some syntax on creating a query using a linked server system.

-jamie

[Former Member]

Thanks Jamie.  I thought about linking, but not positive how to do it.  I will give it a try.  Thanks again.