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

* 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

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

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

标签：

查看更多关于跨服务器、跨数据库、多表联合查询的详细内容...