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

 

 

OPENDATASOURCE
不使用链接的服务器名，而提供特殊的连接信息，并将其作为四部分对象名的一部分。

语法
OPENDATASOURCE ( provider_name, init_string )

参数
provider_name

注册为用于访问数据源的 OLE DB 提供程序的 PROGID 的名称。provider_name 的数据类型为 char，没有默认值。

init_string

连接字符串，这些字符串将要传递给目标提供程序的 IDataInitialize 接口。提供程序字符串语法是以关键字值对为基础的，这些关键字值对由分号隔开，例如："keyword1=value; keyword2=value."

在 Microsoft? Data Access SDK 中定义了基本语法。有关所支持的特定关键字值对的信息，请参见提供程序中的文档。下表列出 init_string 参数中最常用的关键字。

关键字 OLE DB 属性 有效值和描述
数据源 DBPROP_INIT_DATASOURCE 要连接的数据源的名称。不同的提供程序用不同的方法对此进行解释。对于 SQL Server OLE DB 提供程序来说，这会指明服务器的名称。对于 Jet OLE DB 提供程序来说，这会指明 .mdb 文件或 .xls 文件的完整路径。
位置 DBPROP_INIT_LOCATION 要连接的数据库的位置。
扩展属性 DBPROP_INIT_PROVIDERSTRING 提供程序特定的连接字符串。
连接超时 DBPROP_INIT_TIMEOUT 超时值，在该超时值后，连接尝试将失败。
用户 ID DBPROP_AUTH_USERID 用于该连接的用户 ID。
密码 DBPROP_AUTH_PASSWORD 用于该连接的密码。
目录 DBPROP_INIT_CATALOG 连接到数据源时的初始或默认的目录名称。

注释
OPENDATASOURCE 函数可以在能够使用链接服务器名的相同 Transact-SQL 语法位置中使用。因此，就可以将 OPENDATASOURCE 用作四部分名称的第一部分，该名称指的是 SELECT、INSERT、UPDATE 或 DELETE 语句中的表或视图的名称；或者指的是 EXECUTE 语句中的远程存储过程。当执行远程存储过程时，OPENDATASOURCE 应该指的是另一个 SQL Server。OPENDATASOURCE 不接受参数变量。

与 OPENROWSET 函数类似，OPENDATASOURCE 应该只引用那些不经常访问的 OLE DB 数据源。对于访问次数稍多的任何数据源，请为它们定义链接的服务器。无论 OPENDATASOURCE 还是 OPENROWSET 都不能提供链接的服务器定义的全部功能，例如，安全管理以及查询目录信息的能力。每次调用 OPENDATASOURCE 时，都必须提供所有的连接信息（包括密码）。

示例
下面的示例访问来自某个表的数据，该表在 SQL Server 的另一个实例中。

SELECT    *
FROM       OPENDATASOURCE(
          'SQLOLEDB',
          'Data Source=ServerName;User ID=MyUID;Password=MyPass'
          ).Northwind.dbo.Categories

下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
   'Data Source="c:/Finance/account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

OPENROWSET
包含访问 OLE DB 数据源中的远程数据所需的全部连接信息。当访问链接服务器中的表时，这种方法是一种替代方法，并且是一种使用 OLE DB 连接并访问远程数据的一次性的、特殊的方法。可以在查询的 FROM 子句中像引用表名那样引用 OPENROWSET 函数。依据 OLE DB 提供程序的能力，还可以将 OPENROWSET 函数引用为 INSERT、UPDATE 或 DELETE 语句的目标表。尽管查询可能返回多个结果集，然而 OPENROWSET 只返回第一个。

语法
OPENROWSET ( 'provider_name'
     , { 'datasource' ; 'user_id' ; 'password'
         | 'provider_string' }
     , { [ catalog.] [ schema.] object
         | 'query' }
     )

参数
'provider_name' 字符串，它代表在注册表中指定的 OLE DB 提供程序的友好名。provider_name 没有默认值。
'datasource' 字符串常量，它对应着某个特定的 OLE DB 数据源。datasource 是将被传递到提供程序 IDBProperties 接口以初始化提供程序的 DBPROP_INIT_DATASOURCE 属性。通常，这个字符串包含数据库文件的名称、数据库服务器的名称，或者提供程序能理解的用于查找数据库的名称。
'user_id' 字符串常量，它是传递到指定 OLE DB 提供程序的用户名。user_id 为连接指定安全上下文，并将它作为 DBPROP_AUTH_USERID 属性传递进来以初始化提供程序。
'password' 字符串常量，它是将被传递到 OLE DB 提供程序的用户密码。当初始化提供程序时，将 password 作为 DBPROP_AUTH_PASSWORD 属性传递进来。
'provider_string' 提供程序特定的连接字符串，将它作为 DBPROP_INIT_PROVIDERSTRING 属性传递进来以初始化 OLE DB 提供程序。通常 provider_string 封装初始化提供程序所需的所有连接信息。
catalog 目录或数据库的名称，其中驻留着指定的对象。
schema   架构的名称或指定对象的对象所有者名称。
object   对象名称，它唯一地标识出将要操作的对象。
'query' 是字符串常量，发送到提供程序并由提供程序执行。Microsoft? SQL Server? 不处理该查询，但处理由提供程序返回的查询结果（直接传递查询）。对于有些提供程序，它们并没有通过表名而是通过命令语言表现自己的表格格式数据，那么将直接传递查询用于这些提供程序是非常有用的。只要查询提供程序支持 OLE DB Command 对象及其强制接口，那么在远程服务器上就支持直接传递查询。有关更多信息，请参见 SQL Server OLE DB 程序员参考。

注释
如果 OLE DB 提供程序在指定的数据源中支持多个目录和架构，那么就需要目录及架构名称。如果 OLE DB 提供程序并不支持目录和架构，那么可以省略 catalog 及 schema 的值。

如果提供程序只支持架构名，那么必须指定一个两部分名称，形式为 schema.object。如果提供程序只支持目录名，那么必须指定一个三部分名称，形式为 catalog.schema.object。

OPENROWSET 不接受参数变量。

权限
OPENROWSET 权限由传递到 OLE DB 提供程序的用户名的权限确定。

示例
A. 将 OPENROWSET 与 SELECT 语句及用于 SQL Server 的 Microsoft OLE DB 提供程序一起使用
下面的示例使用用于 SQL Server 的 Microsoft OLE DB 提供程序访问 pubs 数据库中的 authors 表，该数据库在一个名为 seattle1 的远程服务器上。从 datasource、user_id 及 password 中初始化提供程序，并且使用 SELECT 语句定义返回的行集。

USE pubs
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
    'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
GO

B. 将 OPENROWSET 与对象及用于 ODBC 的 OLE DB 提供程序一起使用
下面的示例使用用于 ODBC 的 OLE DB 提供程序以及 SQL Server ODBC 驱动程序访问 pubs 数据库中的 authors 表，该数据库在一个名为 seattle1 的远程服务器中。提供程序用在 ODBC 提供程序所用的 ODBC 语法中指定的 provider_string 进行初始化，定义返回的行集时使用 catalog.schema.object 语法。

USE pubs
GO
SELECT a.*
FROM OPENROWSET('MSDASQL',
    'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',
    pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname
GO

C. 使用用于 Jet 的 Microsoft OLE DB 提供程序
下面的示例通过用于 Jet 的 Microsoft OLE DB 提供程序访问 Microsoft Access Northwind 数据库中的 orders 表。

说明   下面的示例假定已经安装了 Access。
USE pubs
GO
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'c:/MSOffice/Access/Samples/northwind.mdb';'admin';'mypwd', Orders)
    AS a
GO

D. 使用 OPENROWSET 和 INNER JOIN 中的另一个表
下面的示例从本地 SQL Server Northwind 数据库的 customers 表中，以及存储在相同计算机上 Access Northwind 数据库的 orders 表中选择所有数据

说明   下面的示例假定已经安装了 Access。

USE pubs
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c INNER JOIN
    OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'c:/MSOffice/Access/Samples/northwind.mdb';'admin';'mypwd', Orders)
    AS o
    ON c.CustomerID = o.CustomerID
GO

上面两个方法的示例:
[OpenDataSource]
    1.
     Select * from OpenDataSource('SQLOLEDB','Data Source=ServerName;User ID=MyID;Password=MyPass').DBName.DBO.TableName
    2.通过用Jet的OLEDB提供程序查询Excel电子表格
     Select * from OpenDataSource('MICROSOFT.JET.OLEDB.4.0','Data Source="c:/account.xls";User ID=Admin;Password=;Extended Properties=Excel 5.0')...Xactions

[OpenRowSet]
    格式:
      OpenRowSet('Provider_Name','{'DataSource';'UserID';'Password'|'Provider_String'},{[CateLog.][Schema.]Object|'Query'})
    1.
     Select A.* from OpenRowSet('SQLOLEDB','ServerName';'UserID';'Password','Select * from pubs.dbo.authors',Orders) as A
    
    2.
     Select A.* from OperRowset('MSDASQL','DRIVER={SQL Server};Server=ServerName;UID=UserID;PWD=MyPass',pus.dbo.Authors) as A
   
    3.Select A.* from OperRowSet('MICROSOFT.JET.OLEDB.4.0','c:/access/northowind.mdb';'UserID';'Password',Orders) AS A
   
    4.Select C.* ,O.* from Northowind.dbo.customers   c
     inner join OpenRowSet('MICROSOFT.JET.OLEDB.4.0','c:/access/northowind.mdb';'Admin';'MyPass',Orders) as o
     on c.CustomerID = o.CustomerID
   
    5.Select * From OpenRowSet('MICROSOFT.JET.OLEDB.4.0','EXCEL 5.0;HDR=YES;IMEX=2;DataBase=D:/Book1.XLS',[sheet1$])