如何从SQL Server中两个不同服务器上的两个不同数据库中选择同一查询中的数据?


当前回答

 select * 
 from [ServerName(IP)].[DatabaseName].[dbo].[TableName]

其他回答

跨2个不同的数据库进行查询是一种分布式查询。以下是一些技巧及其优缺点:

Linked servers: Provide access to a wider variety of data sources than SQL Server replication provides Linked servers: Connect with data sources that replication does not support or which require ad hoc access Linked servers: Perform better than OPENDATASOURCE or OPENROWSET OPENDATASOURCE and OPENROWSET functions: Convenient for retrieving data from data sources on an ad hoc basis. OPENROWSET has BULK facilities as well that may/may not require a format file which might be fiddley OPENQUERY: Doesn't support variables All are T-SQL solutions. Relatively easy to implement and set up All are dependent on connection between source and destionation which might affect performance and scalability

SELECT
        *
FROM
        [SERVER2NAME].[THEDB].[THEOWNER].[THETABLE]

您还可以使用链接服务器。链接服务器也可以是其他类型的数据源,比如DB2平台。这是一种尝试从SQL Server TSQL或Sproc调用访问DB2的方法…

试试这个:

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=YOUR SERVER;Trusted_Connection=yes;','SELECT * FROM Table1') AS a
UNION
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=ANOTHER SERVER;Trusted_Connection=yes;','SELECT * FROM Table1') AS a
sp_addlinkedserver('servername')

所以它应该是这样的

select * from table1
unionall
select * from [server1].[database].[dbo].[table1]

您正在寻找的是链接服务器。你可以从对象资源管理器树中的以下位置在SSMS中找到它们:

服务器对象——>链接服务器

或者您可以使用sp_addlinkedserver。

你只需要建立一个。一旦你有了这个,你可以像这样调用另一个服务器上的表:

select
    *
from
    LocalTable,
    [OtherServerName].[OtherDB].[dbo].[OtherTable]

注意,所有者并不总是dbo,因此请确保将其替换为您使用的任何模式。