1

Lets say we have two databases, one named db1, the other one named db2. Both databases have a table called Employees. Is there anyway to join results from both tables even though they have the same name?

Note that the two tables do NOT have conflicting field names. I know this is stupid but we had to make an extension database to the existing one and to keep it simple we'd like to have the same table name in both databases.

Help is greatly appreciated, thanks

Also I know there is a similar question answered here but I didn't find it helpful in this context.

Jonas B
  • 2,351
  • 2
  • 18
  • 26

4 Answers4

1

Yes you can definitely do this, you'll just need to alias them like so:

SELECT ... FROM [db1].[dbo].[Employees] as e1 INNER JOIN [db2].[dbo].[Employees] as e2 ON ...

Then you can get what you need by referencing e1.MyColumn or e2.MyOtherColumn.

Justin Swartsel
  • 3,451
  • 1
  • 20
  • 24
1

You should be able to refer to the tables by their fully-qualified names. If they're in different schemas, you can say:

SELECT * FROM Schema1.MyTable as T1 JOIN Schema2.MyTable as T2 ON T1.Something = T2.SomethingElse

If they're actually different databases, you'd need a database link in which case it becomes MyTable1@Database1 etc.

Dan
  • 10,990
  • 7
  • 51
  • 80
  • 1
    Thanks a lot, I don't know why I tried using joins when it's that simple. Thumbs up mate! Thanks to everyone else who posted as well, but I'll award this to this guy who posted first, it's only fair I think. – Jonas B Dec 16 '09 at 15:15
0

You should be able to reference them by database.owner.table, e.g. db1.dbo.Employees and db2.dbo.Employees (assuming the tables are owned by dbo)

Joe
  • 41,484
  • 20
  • 104
  • 125
  • Yes I tried that but the query failed instantly after adding INNER JOIN db2.Employees ON db2.Employees.EmpId = db1.Employees.UUID The data exists in both tables so the join itself should not fail, that's why I thought it was a problem with using the same table name. – Jonas B Dec 16 '09 at 15:12
-1

select * from db1,db2 where db1.employee=db2.employee Is this what you mean?

Grumpy
  • 2,140
  • 1
  • 25
  • 38