Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    7

    Unanswered: Multiple databases and SQL

    I am facing a problem within SQL server. Is it possible to create SQL query to two separate databases at the same time ?

    There are logical relations between tables in separate databases and I need to create some JOINs between tables in separate dbs into my SQL query. I there any solutions to this problem ?

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    is the databases are on the same server you just need to use a three part nameing convention <Database Name>.<Owner Name>.<Table Name>

    So if in db1 you have a table named tbl1 owned by usr1 and in db2 you have a table named tbl2 owned by dbo you would...

    Code:
    select *
      from db1.usr1.tbl1 t1
      join db2.dbo.tbl2 t2 on t1.ParentKey = t2.ChildKey
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Nov 2002
    Posts
    9

    Re: Multiple databases and SQL

    On the same server?
    If so, just do a
    select * from database1.owner1.table1
    join database2.owner2.table2 on table1.field1=table2.field2

    If the're on different servers (but still mssql) then use a linked server (heterogenous join)
    select * from server1.database1.owner1.table1
    join server2.database2.owner2.table2 on table1.field1=table2.field2

    If it's a different dbms, use openquery or openrowset (depending on what exactly you're trying to do)


    Originally posted by asset1
    I am facing a problem within SQL server. Is it possible to create SQL query to two separate databases at the same time ?

    There are logical relations between tables in separate databases and I need to create some JOINs between tables in separate dbs into my SQL query. I there any solutions to this problem ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •