试试这个:

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]

添加链接服务器的简化解决方案

第一个服务器

EXEC sp_addlinkedserver @server='ip,port\instancename'

第二次登录

EXEC sp_addlinkedsrvlogin 'ip,port\instancename', 'false', NULL, 'remote_db_loginname', 'remote_db_pass'

执行从链接到本地db的查询

INSERT INTO Tbl (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM [ip,port\instancename].[linkedDBName].[linkedTblSchema].[linkedTblName]

正如@Super9所说的，OPENDATASOURCE使用数据提供者的SQL Server身份验证 SQLOLEDB ． 我只是在这里发布一个代码片段，一个表是在当前服务器数据库中运行的代码，另一个在其他服务器'192.166.41.123'中

SELECT top 2 * from dbo.tblHamdoonSoft  tbl1 inner JOIN  
OpenDataSource('SQLOLEDB','Data Source=192.166.41.123;User ID=sa;Password=hamdoonsoft')
.[TestDatabase].[dbo].[tblHamdoonSoft1] tbl2 on tbl1.id = tbl2.id

跨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

在一台服务器中为另一台服务器创建链接服务器定义(需要SA来完成此操作)，然后使用四部分命名引用它们(参见BOL)。