Querying data by joining two tables in two database on different servers

Question

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

User · Answer

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

User · Answer

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- gt connect error        die  Error        mysqli1- gt connect errno         mysqli1- gt connect error     else    echo  DB1 open OK lt br gt      if   mysqli2- gt connect error        die  Error        mysqli2- gt connect errno         mysqli2- gt connect error     else    echo  DB2 open OK lt br gt  lt br gt        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- gt query  SELECT   FROM video where video id old is NULL        while  row    results- gt fetch array               theID    row 0           echo  Original ID      theID    lt br gt             doInsert    mysqli2- gt query  INSERT INTO video       VALUES                   doGetVideoID    mysqli2- gt query  SELECT video id  time stamp from video where user id       row 13     and time stamp      row 28                    while  row    doGetVideoID- gt fetch assoc                      echo  New video id      row  video id     user id      row  user id     time stamp      row  time stamp     lt br gt                     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- gt close     mysqli2- gt 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Querying data by joining two tables in two database on different servers

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to sql-server-2005

Examples related to sql-server-2008