SQL Server中跨不同托管数据库查询方案（无权限添加用户及链接服务器）

针对两个数据库处于不同托管环境、无法给同一用户授权双库访问、也不能添加链接服务器的情况，以下是几种可行的实现方式：

1. 导出-导入+本地更新脚本 #

• 操作步骤：
  1. 在SSMS中连接Database B，右键数据库 → 任务 → 导出数据，将需要的数据集导出为CSV、Excel或SQL脚本文件。
  2. 切换连接到Database A，将导出的文件导入到临时表（比如#Temp_B_Data）中。
  3. 编写关联更新语句：

     UPDATE A_Table
     SET A_Target_Column = #Temp_B_Data.Source_Column
     FROM A_Table
     JOIN #Temp_B_Data ON A_Table.Id = #Temp_B_Data.Id
     

• 适用场景：数据量较小、不需要频繁同步的一次性更新需求。

2. 使用SSIS（SQL Server Integration Services） #

• 操作方式：
  1. 本地安装SSIS工具，创建新的SSIS项目。
  2. 分别配置Database A和Database B的连接管理器（各自使用对应环境的权限账号）。
  3. 添加数据流任务，从Database B读取数据，通过“查找”或“合并连接”组件关联Database A的目标表，配置更新规则完成同步。
  4. 可将包部署到本地或设置调度，实现定期自动同步。
• 适用场景：需要定期、自动化同步数据的场景，支持复杂的数据转换逻辑。

3. PowerShell脚本批量处理 #

• 示例脚本思路：
  1. 预先安装SqlServer模块：Install-Module SqlServer。
  2. 分别建立两个数据库的连接，从Database B查询待更新数据：

     # 获取Database B的数据
     $bData = Invoke-SqlCmd -ServerInstance "B_Server_Name" -Database "Database_B" -Query "SELECT Id, Update_Column FROM B_Table"
     
     # 连接Database A执行更新
     foreach ($row in $bData) {
         Invoke-SqlCmd -ServerInstance "A_Server_Name" -Database "Database_A" -Query "UPDATE A_Table SET Target_Column = '$($row.Update_Column)' WHERE Id = $($row.Id)"
     }
     

• 适用场景：适合编写自动化脚本，灵活处理数据逻辑，无需依赖SSIS环境。

4. 使用OPENROWSET/OPENDATASOURCE（需环境支持） #

• 前提：Database A所在的SQL Server实例允许启用Ad Hoc Distributed Queries配置，且你有足够权限执行相关操作。
• 操作步骤：
  1. 启用配置（仅需执行一次）：

     sp_configure 'show advanced options', 1;
     RECONFIGURE;
     sp_configure 'Ad Hoc Distributed Queries', 1;
     RECONFIGURE;
     

  2. 直接在Database A中编写跨库更新语句：

     UPDATE A_Table
     SET A_Target_Column = B_Source.Source_Column
     FROM A_Table
     JOIN OPENROWSET(
         'SQLNCLI',
         'Server=B_Server_Name;Trusted_Connection=yes;',
         'SELECT Id, Source_Column FROM Database_B.dbo.B_Table'
     ) AS B_Source ON A_Table.Id = B_Source.Id
     

• 注意：此方法需要Database A所在实例的管理员开启相关配置，需注意连接信息的安全风险。

内容的提问来源于stack exchange，提问作者Ashkan Mobayen Khiabani