How Can Access Data From Multiple Database

[scorpion1983]

HI
Did any one give me a suggestion how to get data from multiple database
e.g

A table "EMP" in Db A

A table "EMP2" in Db B

i want to fethc from both at same time



Best Regards
Scorpion

[Pinky98]

best way is to open two connections, one to ech db, the fetch each seperately. Other wise you can stipulate alternate databases in the FROM clause of a SQL statement... but not all databases support this feature of SQL-92.

[Boumxyz2]

You will need to specify the owner of the tables if you choose Pinky98 suggestion.

Example in SQL Server


SELECT * FROM DB1.DBO.TABLE1 JOIN DB2.DBO.TABLE2

the owner is DBO.. if only 1 table named like this you don't need to specify the owner.. like this :


SELECT * FROM DB1..TABLE1 JOIN DB2..TABLE2

Note : the .. is intended.. IT tells SQL Server to get a table named Table1 no matter who's the owner.

Not all Database supports this. (SQL Server does and so does Oracle but in oracle, you specify the Owner first I think)

SELECT * FROM OWNER.DB1.TABLE1

anyway if both Databases are on the same server it will work.


If databases are on different server.

in SQL Server if you link the other server to the first Server you only have to connect to 1 server. So that way you can do this

SELECT * FROM SERVER1.DB1.DBO.Table1 join SERVER2.DB2.DBO.TABLE2

Else you will need 2 connections as Pinky98 stated.