sqlserver垮库查询_SQL Server 跨数据库查询数据的方法

需求

两个服务器上对应的SQL Server数据库服务，关联两张表查询数据。

单表查询

select * from OPENDATASOURCE('SQLOLEDB','Data Source=远程实列名;User ID=sa;Password=密码').库名.dbo.表名

例如：

select * from OPENDATASOURCE('SQLOLEDB','Data Source=192.168.1.131;User ID=sa;Password=123456').BaoGuang_QrCode.dbo.T_BC_BaseInfo

多表关联查询(下列服务器名是随便写的吆)

SELECT * FROM OPENROWSET('SQLOLEDB','192.168.1.128';'sa';'ele3561424',EleList.dbo.barCode) A

JOIN OPENROWSET('SQLOLEDB','192.168.1.131';'sa';'123456',BaoGuang_QrCode.dbo.T_BC_BaseInfo) B

ON A.BarCode = B.BarCode

WHERE A.createDate>'2019-12-31 00:00:00'

select * from OPENROWSET('SQLOLEDB','192.168.10.28';'sa';'ele3561424',EleList.dbo.barCode) D

where D.BarCode not in (

SELECT A.BarCode FROM OPENROWSET('SQLOLEDB','192.168.10.28';'sa';'ele3561424',EleList.dbo.barCode) A

JOIN OPENROWSET('SQLOLEDB','192.168.10.168';'sa';'123qwe',BaoGuang_QrCode.dbo.T_BC_BaseInfo) B

ON A.BarCode = B.BarCode

WHERE A.createDate>'2019-12-31 00:00:00')

and D.createDate>'2019-12-31 00:00:00'

and D.BarCode like'%2001%'