Joining Tables in Different DB''s

  • Does anyone have any ideas on how to join tables in different DB's without the possiblility of linked servers or replication? I am looking for something along the line of a Data Access Layer in .Net.

  • Do you mean different DB's on the same server or different DB's on different servers?

    If the databases are on the same server, no problem just qualify the databases and tables in your query (e.g. dbnamea.dbo.tablename, dbnameb.dbo.tablename...)

    If the databases are on different SQL Servers a linked server is your best bet for one very simple reason - a lot of time and effort has been spent on making the query engine/optimizer handle remote queries efficiently.  Given a linked SQL Server, SQL Server will attempt of optimize/coordinate the retrieval of the remote data if at all possible.  For example, if you are selecting data from a remote table and it is possible to filter the data on the remote server before returning it to the local server, SQL Server will do so (e.g. if you're querying against a remote "customers" table and your criteria include a sargeable criteria like say customerid as part of the join or where clause criteria, SQL Server will retrieve only the necessary rows from the remote server).

    Joe

     

  • if you are doing it in your DAL, you can use a datatable/dataset into which you populate data from two different connections provided you have the same column names and types. alternatively you can use a business object. below is the pseudocode

    Customer Class

    Customers Class : Collection of Customers

    in Customers class create a proc that will connect to server 1 and get all the customers and add them to the collection

    in the same proc point your connection to a different server and get all the customers add them to the collection

     

    But then you will need to keep track of which db server the customer is stored on if you want to do any updates.

    i would suggest connecting to a single instance of a server when doing any updates e.g. if a person wants to view and update EMEA data they should log into that db and if they want to view and edit China data they should log into the China db

     


    Everything you can imagine is real.

  • Hi,

      My first message in this site.....

    Its not advisable to connect to two different server to do the operation.  If you had access to two different server. whether you are trying to retrieve those datas and do manipulation in the business logic layer.... oops its little bit tedious job and if the datas are huge... then my web application will loose its patience to give a desired output. Instead you can have a linked server with one connection to db from dal...... is advisable. Because, my data filteration will be happening in the backend itself.

     

    Thanks and Regards,

    Venkatesan prabu. J

     

    Thanks and Regards,
    Venkatesan Prabu, 😛
    My Blog:

    http://venkattechnicalblog.blogspot.com/

  • select crap from tbljunk inner join on uselessdata..tblmorejunk on stupidcolumn = dumbercolumn

  • I am glad you throughly read the question before posting because if you had, you would know that you can't execute the following statement across two different SQL Servers. The statement will only work if it is run on different databases on the same server.

  • ... so the 4th qualifier is missing in one place ... bfd ... that's what QA and etsting are for ...

    ... besides that ... it is a really cool query ! ! !

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • Deltatuk -

    I just have to ask, why can't you use linked servers or replication?  Sounds to me like somebody has made a decision without all of the facts.

    Joe

     

  • get a copy of MS Access, dump your data into the Access db via linked tables and run your queries against the access db

  • Thanks for the reference to a non-enterprise DB (Access). We prefer something a little more robust. As for linked servers and replication. We can use replication but it seems like such a waste to replicate a entire db (50 gig) or even select tables to another server so that we do joins on them. As for linked servers the DBA's have put their foot down on not using that.

  • what's wrong with linked servers?

    the 50GB is only for the initial snapshot if you replicate all the tables. after that it's only the changes replicated. we replicate tables to a lot of subscribers into many different databases. you don't have to replicate the entire database.

    this is what replication was made for. where is the waste?

    you're just putting yourself into a corner refusing to use standard tools for accessing data across servers the easy way

  • Deltatuk,

    How many rows (approximately) are there in the data sources? The reason I am asking is- do you think that moving the entire set of data from the source table(s) and then joining after moving the data- is that a feasible solution? Or do you need to worry about the optimization of the JOIN and the WHERE clauses within the SQL statement (e.g., in the case of large tables)?

    There are some solutions to the above, but the appropriate answer will require more info, as per the question above.

    In DB2 and federated database systems, the JOIN is built in natively in the database, server, and client tools. Similar options are not available in SQL server (yet). However, like I said before, there may be some architectural work-arounds.

    In regards to the postings above- there are many cases where architectural, network, and security constraints that would prevent one from using linked servers, RPC calls, etc. The question asked is very valid, as the same features are available in other DBMS systems.

  • Another option might be to use the Merge Join transformation available in SSIS.  It allows you to perform a join on disparate data sources when a normal SQL query join is not possible.

  • Is CLR Integration enabled?  If it is you could write an assembly that accesses the remote data and use the assmbly to create a Table Valued function you can then join to.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply