0

i am trying to figure out the syntax to do a SQL inner join from 2 different databases...

The sameple code looks like this: select *
from database1.dbo.table1 t1 join database2.dbo.table2 t2 on
t1.field1 = t2.field2

but I dont know how to refernce the " database1.dbo" and "database2.dbo" parts.. can someone help?

i know the table names in all my databases.. i have the connections in include files..

i just dont see how to "call" the other database? basically what i to know is what need to change dqatabase1, database 2, and dbo to in my code?

5
Contributors
6
Replies
28
Views
3 Years
Discussion Span
Last Post by kgariando
0

What does the error message say?

If they're on the same server you just have to put the name of the database before the schema and table names. For example:

SELECT * FROM Database1.dbo.Table1 db1t
INNER JOIN Database2.dbo.Table2 db2t
    ON db1t.ID = db2t.ID

You should also list the specific fields you want to pull from each table rather than using SELECT *.

Edited by ChrisHunter

0

they are on the same server... but whenever i try to change to change the datbase name from Database1.dbo.Table1 it says invalid object name.

0

Hmm.. What are your database names? What are your table name? For example, you have 2 database -- mydb1 and mydb2. Each database have the same table named mytab1. The query could be...

SELECT *
FROM mydb1.mytab1
  INNER JOIN mydb2.mytab1
    ON mydb1.mytab1.id = mydb2.mytab1.id;
0

Check the spelling of you database name, schema name and table name.

The best thing to do will be to either use intilesence or copy and paste the names to make sure you've got the exact spelling as you might have miss spelled them when you created them (I speak from experience).

Edited by ChrisHunter

0

. Create a linked server in DB invironment, then create a SP to take care of it.

  1. Get two DataSets for them, then merge two datatables into one based on usersID.

or try this:

SELECT a.userID, b.usersFirstName, b.usersLastName FROM databaseA.dbo.TableA a inner join database B.dbo.TableB b ON a.userID=b.userID

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.