LInk two tables from two databases

[imtmub]

Hi,
I have two different databases in our company for diffrent departments. for some reason i need to connect few tables to another database.So i created tables in the other database which i need to link. If anybody know to link the table i m very greatful.
Thanks
imt

[almaz]

Hi,
I have two different databases in our company for diffrent departments. for some reason i need to connect few tables to another database.So i created tables in the other database which i need to link. If anybody know to link the table i m very greatful.
Thanks
imt

Are these databases reside on the same SQL Server? If yes, than you don't have to "link" tables, just use them :). For example, you have a table1 in database1 and you want to join it to table2 in database2. Than you can use the following script:

select * 
from table1 t1 inner join database2..table2 t2 on t1.ID = t2.ID

[iburyak]

Just in case two servers are not linked to each other but you still have access to another server you can try query below:

[PHP]select *
from table1 t1
inner join OPENDATASOURCE(
'SQLOLEDB',
'Data Source=server_name;User ID=UserID;Password=password'
).database_name.dbo.table2 t2 on t1.ID = t2.ID[/PHP]

[imtmub]

Just in case two servers are not linked to each other but you still have access to another server you can try query below:

[PHP]select *
from table1 t1
inner join OPENDATASOURCE(
'SQLOLEDB',
'Data Source=server_name;User ID=UserID;Password=password'
).database_name.dbo.table2 t2 on t1.ID = t2.ID[/PHP]

Thanks i have two diffrent server, i hope the second one will work.

[almaz]

Thanks i have two diffrent server, i hope the second one will work.

If you want to link servers on a permanent basis, see linked servers

[imtmub]

If you want to link servers on a permanent basis, see linked servers

Hi,
in my tables t1.ID = t2.ID is not matching. bcz t2.id is new table. if anybody enter the data in table 1 in real time need to copy in table2. Not all the fields but few fields i need to copy.

Thanks
imt

[iburyak]

If you want to link servers on a permanent basis, see linked servers

By the way for security and other reasons it is not allowed to link servers in my company.

[Pratibha Karpe]

It works only when you refer table1 as database1..table1