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%'