How do you join tables from different databases?

• subha.msc2007

  Mr or Mrs. 500

  Points: 549

  More actions

  January 7, 2010 at 5:22 am

  #208711

  plz help me..........

• Kranthi Babu

  Ten Centuries

  Points: 1094

  More actions

  March 24, 2010 at 5:36 am

  #1138807

  Are you still looking out the answer for this.?

  Below is a sample query to join two tables present in two different databases of the same server.

  select a.* from DB1.dbo.[Table] a

  inner join DB2.dbo.[Table] b

  on a.[Col]=b.[Col]

• steve block

  SSCertifiable

  Points: 7698

  More actions

  March 29, 2010 at 9:52 am

  #1141527

  Besides using the 3 part name of a table as previous poster indicated (db.owner.table) you could create a view of a table in the current database that selects from the other database. You can then use the view in the joins.

  I've read that you can create a view of a table on another server or instance. Make sure that the user has access to both servers (same password if SS login) and use "exec sp_serveroption 'Server\InstanceName', 'data access', 'true'". You would then use a 4 part name (server.db.owner.table).

  Steve