Querying data by joining two tables in two databas

问题:

There are two tables in two different databases on different servers, I need to join them so as to make few queries. What options do I have? What should I do?

回答1:

You\'ll need to use sp_addlinkedserver to create a server link. See the reference documentation for usage. Once the server link is established, you\'ll construct the query as normal, just prefixing the database name with the other server. I.E:

-- FROM DB1
SELECT *
FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1
    INNER JOIN [DB2].[MyDatabaseOnDB2].[dbo].[MyOtherTable] tab2
        ON tab1.ID = tab2.ID

Once the link is established, you can also use OPENQUERY to execute a SQL statement on the remote server and transfer only the data back to you. This can be a bit faster, and it will let the remote server optimize your query. If you cache the data in a temporary (or in-memory) table on DB1 in the example above, then you\'ll be able to query it just like joining a standard table. For example:

-- Fetch data from the other database server
SELECT *
INTO #myTempTable
FROM OPENQUERY([DB2], \'SELECT * FROM [MyDatabaseOnDB2].[dbo].[MyOtherTable]\')

-- Now I can join my temp table to see the data
SELECT * FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1
    INNER JOIN #myTempTable tab2 ON tab1.ID = tab2.ID

Check out the documentation for OPENQUERY to see some more examples. The example above is pretty contrived. I would definitely use the first method in this specific example, but the second option using OPENQUERY can save some time and performance if you use the query to filter out some data.

回答2:

Try this:

SELECT tab2.column_name  
FROM  [DB1.mdf].[dbo].[table_name_1] tab1 INNER JOIN [DB2.mdf].[dbo].[table_name_2]  tab2   
    ON tab1.col_name = tab2.col_name

回答3:

If a linked server is not allowed by your dba, you can use OPENROWSET. Books Online will provide the syntax you need.

回答4:

From a practical enterprise perspective, the best practice is to make a mirrored copy of the database table in your database, and then just have a task/proc update it with delta\'s every hour.

回答5:

A join of two tables is best done by a DBMS, so it should be done that way. You could mirror the smaller table or subset of it on one of the databases and then join them. One might get tempted of doing this on an ETL server like informatica but I guess its not advisable if the tables are huge.

回答6:

If the database link option is not available, another route you could take is to link the tables via ODBC to something such as MS Access or Crystal reports and do the join there.

回答7:

I tried this code below and it\'s working fine

SELECT        TimeTrackEmployee.StaffID
FROM            dbo.tblGBSTimeCard AS GBSTimeCard INNER JOIN
                         TimeTrak.dbo.tblEmployee AS TimeTrackEmployee ON GBSTimeCard.[Employee Number] = TimeTrackEmployee.GBSStaffID

回答8:

You could try the following:

select customer1.Id,customer1.Name,customer1.city,CustAdd.phone,CustAdd.Country
from customer1
inner join [EBST08].[Test].[dbo].[customerAddress] CustAdd
on customer1.Id=CustAdd.CustId

回答9:

Maybe hard-coded database names isn\'t the best approach always within an SQL-query. Thus, adding synonyms would be a better approach. It\'s not always the case that databases have the same name across several staging environments. They might consist by postfixes like PROD, UAT, SIT, QA and so forth. So be aware of hard-coded queries and make them more dynamic.

Approach #1: Use synonyms to link tables between databases on the same server.

Approach #2: Collect data separately from each database and join it in your code. Your database connection strings could be part of your App-server configuration through either a database or a config file.

回答10:

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.

$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

回答11:

for this simply follow below query

select a.Id,a.type,b.Name,b.City from DatabaseName.dbo.TableName a left join DatabaseName.dbo.TableName b on a.Id=b.Id

Where I wrote databasename, you have to define the name of the database. If you are in same database so you don\'t need to define the database name but if you are in other database you have to mention database name as path or it will show you error. Hope I made your work easy