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?
This question is related to
sql
sql-server
database
sql-server-2005
sql-server-2008
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.
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.
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.
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.
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
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
If a linked server is not allowed by your dba, you can use OPENROWSET. Books Online will provide the syntax you need.
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
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
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
Source: Stackoverflow.com