跨服务器、跨数据库、多表联合查询

* from ( select * from opendatasource ( ‘SQLOLEDB‘, ‘Data Source=192.168.1.136;User ID=sa;Password=000‘ ).UserB.dbo.UserTB as t1 ) as tt1 join ( select * from opendatasource ( ‘SQLOLEDB‘, ‘Data Source=192.168.1.125;User ID=sa;Password=123‘ ).UserC.dbo.User_TC as t1 ) as tt2 on tt1.UserID=tt2.UserID

上面的代码虽然可以查询出来,但是给人一种繁琐的感觉,那么我们应该如何简化它呢,就想到了视图。

首先将服务器B和C 的表在服务器A生成视图

create View View_User_B
as
(
select
    *
from opendatasource
(
    ‘SQLOLEDB‘,
    ‘Data Source=192.168.1.136;User ID=sa;Password=000‘
).UserB.dbo.UserTB as t1
) as tt1
)

create View View_User_C
as 
(
 select
    *
from opendatasource
(
    ‘SQLOLEDB‘,
    ‘Data Source=192.168.1.125;User ID=sa;Password=123‘
).UserC.dbo.User_TC as t1
)

那么我们就可以简化查询了：

select * from dbo.View_User_B as  t1  join View_User_C as t2 on t1.UserID=t2.UserID

这样就是实现了跨服务器,跨数据库多表联查了

跨服务器、跨数据库、多表联合查询

标签：