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.

Answer 2

Which database are you using? Most of databases come with concept called dblinks. You have to create a dblink of database B in database A and then you can create a synonym (not a must but for ease) and use it as if it is table of database A.

Answer 3

Looks like a heterogeneous join (data on disparate servers/technologies etc).

As such, not straightforward. If you can make Namphibian's method work, go that way.

Otherwise, you need to gather the data from both tables to a common location (one or other of the servers 'in play', or a third server/technology solely for the purpose of co-locating the data). Then you can join the data happily. Many ETL Tools work this way, and this situation (almost) always involves redistribution of one or more of the tables to a common location before joining. Oracle Data Integrator ETL tool does this, so does Talend Open Studio's tJoin component.

HTH

Answer 4

SELECT (things)
FROM databaseA.dbo.table t1
INNER JOIN databaseB.dbo.table t2 ON t1.Col1 = t2.Col2
WHERE t1.Col1 = 'something'

EDIT - This statement should meet the new requirements:

SELECT *
FROM databaseA.dbo.table t1
INNER JOIN databaseB.dbo.table t2 ON t1.ID = t2.ID
WHERE t1.Age = 10 AND t2.FavoriteColor = 'Blue'

Answer 5

Assuming the databases are on the same server, you should be able to do something like this:

SELECT t.field1, t.field2
FROM database.schema.table t
JOIN database2.scheme.table2 t2 
   on t.id = t2.id
WHERE t2.field3 = ...

If the databases are on separate servers, look into using Linked Servers.

Answer 6

Try creating 3 Linq queries in Visual Studio. One for SQL Server, one for Oracle and one to combine the 2 database objects.

Answer 7

If you want to select data from two different servers and database I would do a union and not a join as the data from one may be like apples and the other may be like oranges. You still would need to set up linked Servers and I believe you may link Oracle and SQL Server if after certain versions as shown but you could do something like this:

select ColA, ColB, ColC
from (ServerASQLServer).(DatabaseA).(schema).(table)
UNION
select ColA, ColB, ColC
from (ServerBOracleServer).(DatabaseB).(schema).(table)

If you perform inner joins your data must share data types to bind to or else they will be ommitted from the dataset returned. A union must just shared column data types but does not care on the logic. You are in essence saying: "Put these two sets of varying rows together based on their column logic matching."

But you were mentioning connection strings so I was curious if you would want to do it in a type of code method like .NET? I could provide an idea for that too possibly.

Answer 8

While I was having trouble join those two tables, I got away with doing exactly what I wanted by opening both remote databases at the same time. MySQL 5.6 (php 7.1) and the other MySQL 5.1 (php 5.6)

//Open a new connection to the MySQL server
$mysqli1 = new mysqli('server1','user1','password1','database1');
$mysqli2 = new mysqli('server2','user2','password2','database2');

//Output any connection error
if ($mysqli1->connect_error) {
    die('Error : ('. $mysqli1->connect_errno .') '. $mysqli1->connect_error);
} else { 
echo "DB1 open OK<br>";
}
if ($mysqli2->connect_error) {
    die('Error : ('. $mysqli2->connect_errno .') '. $mysqli2->connect_error);
} else { 
echo "DB2 open OK<br><br>";
}

If you get those two OKs on screen, then both databases are open and ready. Then you can proceed to do your querys.

On your specific question I will do something like first selecting from database A all the 10 year old kids then match them to the colors by the ID from database B. It should work, I havent tested this code on my server, but my sample below this code works. You can custom query by anything, color, age, whatever, even group them as you require to.

$results = $mysqli1->query("SELECT * FROM DatabaseTableA where age=10");
    while($row = $results->fetch_array()) {
        $theColorID = $row[0];
        $theName = $row[1];
        $theAge = $row[2];
        echo "Kid Color ID : ".$theColorID." ".$theName." ".$theAge."<br>";
        $doSelectColor = $mysqli2->query("SELECT * FROM DatabaseTableB where favorite_color=".$theColorID." "); 
        while($row = $doSelectColor->fetch_assoc()) {
            echo "Kid Favorite Color : ".$row["favorite_color"]."<br>";
        }
    }

I have use this to switch back and forth for our programs without joining tables from remote servers and have no problem so far.

$results = $mysqli1->query("SELECT * FROM video where video_id_old is NULL");
    while($row = $results->fetch_array()) {
        $theID = $row[0];
        echo "Original ID : ".$theID." <br>";
        $doInsert = $mysqli2->query("INSERT INTO video (...) VALUES (...)");
        $doGetVideoID = $mysqli2->query("SELECT video_id, time_stamp from video where user_id = '".$row[13]."' and time_stamp = ".$row[28]." ");
            while($row = $doGetVideoID->fetch_assoc()) {
                echo "New video_id : ".$row["video_id"]." user_id : ".$row["user_id"]." time_stamp : ".$row["time_stamp"]."<br>";
                $sql = "UPDATE video SET video_id_old = video_id, video_id = ".$row["video_id"]." where user_id = '".$row["user_id"]."' and video_id = ".$theID.";";
                $sql .= "UPDATE video_audio SET video_id = ".$row["video_id"]." where video_id = ".$theID.";";
                // Execute multi query if you want
                if (mysqli_multi_query($mysqli1, $sql)) {
                    // Query successful do whatever...
                }
            }
    }
// close connection 
$mysqli1->close();
$mysqli2->close();

I was trying to do some joins but since I got those two DBs open, then I can go back and forth doing querys by just changing the connection $mysqli1 or $mysqli2

It worked for me, I hope it helps... Cheers

Answer 9

As long as both databases are in the same server you can refer to tables with the database name :)

SELECT * FROM db1.table1
join 
db2.tbable2 
WHERE db1.table1.col1 = db2.table2.col1;