How to get data from two databases on two servers with one SELECT operation?

Question

I really do not want to modify the database, I 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 this possible, especially considering that there is one Oracle and one SQL Server ? Or would it be better to do SELECT statements for each database individually and then match them after?

---

For example, how do I get all students who are 10 years old, and how is the color blue?

Note that all elements in DatabaseB have an identifier that maps to DatabaseA, but not vice versa.

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 server and other MSSQL servers. Then you can directly query the object directly using the linked server or create synonyms for tables of linked servers in your database.

Steps to create and use a linked server:

• On your “primary” MSSQL server, create two linked servers on servers that contain two databases or, as you said, database A and database B.
• Then you can directly query tables on linked servers using simple TSQL select statements.

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

A bit more about synonyms. If you intend to use these related server tables in LOT requests, it might be worth using synonyms to maintain the code for you. A synonym allows you to refer to something under a different name.

So, for example, when selecting data from a linked server, you usually use the following syntax to retrieve data:

 SELECT * FROM Linkedserver.database.schema.table 

If you created a synonym for Linkedserver.database.schema.table as DBTable1, the syntax will look like this:

 SELECT * FROM DBTable1 

This will save a bit on input plus, if your linked server has ever changed, you will not need to make changes throughout your code. As I said, this can be beneficial if you use linked servers in a lot of code.

In a more careful note, you can make a connection between two tables on different servers. Although it is usually painfully slow. I found that you can select data from different servers in temp tables, and joining temporary tables can generally speed up the process. Your objection may change, but if you intend to join tables on different servers, this method may help.

Let me know if you need more information.

Answer 2

What database are you using? Most databases have the concept of dblinks. You have to create dblink of database B in database A, and then you can create a synonym (not mandatory, but for convenience) and use it as if it were a database table A.

Answer 3

It looks like a heterogeneous connection (data on disparate servers / technologies, etc.).

So, not easy. If you can get the Namphibian method to work, go this way.

Otherwise, you need to collect data from both tables into a shared folder (one or the other of the in-game servers or a third server / technology solely for data sharing). Then you can join the data with joy. Many ETL Tools work this way, and this situation (almost) always involves redistributing one or more tables to a common place before joining. The Oracle Data Integrator ETL tool does this, so the Talend Open Studio tJoin component.

NTN

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 must 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 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, use Linked Servers .

Answer 6

Try creating 3 Linq queries in Visual Studio. One for SQL Server, one for Oracle, and one for combining two database objects.

Answer 7

If you want to select data from two different servers and a database, I would do a join rather than a join, because the data from them might look like apples, and the others might look like oranges. You still need to configure linked servers, and I believe that you can bind Oracle and SQL Server if after certain versions, as shown, but you can 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 are making internal joins, your data must share data types for binding, otherwise they will be excluded from the returned dataset. A join should just separate the column data types, but it doesn't care about the logic. You essentially say: "Put these two sets of different rows together, based on their matching column logic."

But you mentioned the connection strings, so I was curious if you want to do this as a code method like .NET? I could also imagine this idea.

Answer 8

When I had problems joining these two tables, I was able to do exactly what I wanted by opening both remote databases at the same time. MySQL 5.6 (php 7.1) and 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 see these two OKs on the screen, then both databases are open and ready. Then you can begin to fulfill your requests.

For your specific question, I will do something like the first selection in the database A of all 10-year-old children, then match them with the colors by the identifier from database B. This should work, I did not test this code on my server, but my example below this code works. You can perform an arbitrary request for any, color, age, whatever you like, even group them as you need.

 $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 used this to switch between our programs without joining tables from remote servers, and I have no problems 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 tried to make several joins, but since these two databases were open, I can move from one query to another just by changing the connection of $mysqli1 or $mysqli2

It worked for me, I hope this helps ... Hooray

Answer 9

While both databases are on 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;