How to get data from two databases in two servers with one SELECT statement?

Question

I don't actually want to modify either database, just get the data.

I know how to connect to each database individually, with these connection strings:

Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;Persist Security Info=False;

Provider=OraOLEDB.Oracle.1;Data Source={0};User ID={1};Password={2};Persist Security Info=True;

But how can I get this overlapping data together? Is that even possible, especially considering that one is Oracle and one is SQL Server? Or would it be better to do the SELECT statements on each database individually and then match them after?

---

For example, how would I get all students that are 10 years old and like the color blue?

Notice that all items in DatabaseB have an ID that maps to DatabaseA, but not the other way around.

Answer 1

I have done this with MySQL,Oracle and SQL server. You can create linked servers from a central MSSQL server to your Oracle and other MSSQL servers. You can then either query the object directly using the linked server or you can create a synonymn to the linked server tables in your database.

Steps around creating and using a linked server are:

1. On your "main" MSSQL server create two linked servers to the servers that contains the two databases or as you said database A and database B.
2. You can then query the tables on the linked servers directly using plain TSQL select statements.

To create a linked server to Oracle see this link: http://support.microsoft.com/kb/280106

A little more about synonyms. If you are going to be using these linked server tables in a LOT of queries it might be worth the effort to use synonymns to help maintain the code for you. A synonymn allows you to reference something under a different name.

So for example when selecting data from a linked server you would generally use the following syntax to get the data:

SELECT *
FROM Linkedserver.database.schema.table

If you created a synonym for Linkedserver.database.schema.table as DBTable1 the syntax would be:

SELECT *
FROM DBTable1

It saves a bit on typing plus if your linked server ever changed you would not need to go do changes all over your code. Like I said this can really be of benefit if you use linked servers in a lot of code.

On a more cautionary note you CAN do a join between two table on different servers. HOwever this is normally painfully slow. I have found that you can select the data from the different server into temp tables and joining the temp tables can generally speed things up. Your milage might vary but if you are going to join the tables on the different servers this technique can help.

Let me know if you need more details.