今天我们来学习如何在SQL Server中跨数据库访问数据，实现不同服务器之间的数据同步。
一、跨数据库访问的三种方式
二、方案
方案一：使用OPENDATASOURCE（临时访问）
CREATE PROCEDURE usp_SyncPersonnelDataASBEGIN SET NOCOUNT ON
BEGIN TRY BEGIN TRANSACTION
SELECT CAST(工号 AS INT) AS PersonNo, 姓名 AS PersonName, 直间接 AS DutyType INTO #TempPersonnel FROM OPENDATASOURCE( 'SQLOLEDB', 'DATA SOURCE=DATABAK;UID=sa;PWD=密码;' ).gwidb.dbo.out_TANXUEMEISYSTEMpawf
UPDATE dp SET Workflag = 'N' FROM syteline.SHATDB.dbo.DeptPerson dp LEFT JOIN #TempPersonnel tp ON dp.PersonNo = tp.PersonNo WHERE tp.PersonNo IS NULL AND dp.Workflag = 'Y'
UPDATE dp SET PersonNo = tp.PersonNo, [Name] = tp.PersonName, Duty = tp.DutyType, Workflag = 'Y' FROM syteline.SHATDB.dbo.DeptPerson dp INNER JOIN #TempPersonnel tp ON dp.PersonNo = tp.PersonNo
INSERT INTO syteline.SHATDB.dbo.DeptPerson ( PersonNo, [Name], Workflag, Duty ) SELECT tp.PersonNo, tp.PersonName, 'Y', tp.DutyType FROM #TempPersonnel tp WHERE NOT EXISTS ( SELECT 1 FROM syteline.SHATDB.dbo.DeptPerson dp WHERE dp.PersonNo = tp.PersonNo )
DROP TABLE #TempPersonnel
COMMIT TRANSACTION PRINT '人员数据同步完成'
END TRY BEGIN CATCH ROLLBACK TRANSACTION DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE() DECLARE @ErrorSeverity INT = ERROR_SEVERITY() RAISERROR(@ErrorMessage, @ErrorSeverity, 1) END CATCHEND
方案二：使用链接服务器（推荐）
EXEC sp_addlinkedserver @server = 'RemoteHR', @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = 'DATABAK'
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'RemoteHR', @useself = 'false', @rmtuser = 'sa', @rmtpassword = '密码'
CREATE PROCEDURE usp_SyncPersonnelData_LinkedServerASBEGIN SET NOCOUNT ON
BEGIN TRY BEGIN TRANSACTION
SELECT CAST(工号 AS INT) AS PersonNo, 姓名 AS PersonName, 直间接 AS DutyType INTO #TempPersonnel FROM [RemoteHR].gwidb.dbo.out_TANXUEMEISYSTEMpawf
DROP TABLE #TempPersonnel COMMIT TRANSACTION
END TRY BEGIN CATCH ROLLBACK TRANSACTION THROW END CATCHEND
方案三：使用OPENQUERY（更灵活）
CREATE PROCEDURE usp_SyncPersonnelData_OpenQueryASBEGIN SET NOCOUNT ON
SELECT * INTO #TempPersonnel FROM OPENQUERY(RemoteHR, ' SELECT CAST(工号 AS INT) AS PersonNo, 姓名 AS PersonName, 直间接 AS DutyType FROM gwidb.dbo.out_TANXUEMEISYSTEMpawf WHERE 直间接 IN (''直'', ''间接'') -- 可以在远程过滤数据 ')
END
三、性能对比测试
CREATE PROCEDURE usp_TestSyncPerformanceASBEGIN DECLARE @StartTime DATETIME, @EndTime DATETIME
SET @StartTime = GETDATE() EXEC usp_SyncPersonnelData SET @EndTime = GETDATE() PRINT 'OPENDATASOURCE耗时：' + CAST(DATEDIFF(ms, @StartTime, @EndTime) AS VARCHAR) + 'ms'
SET @StartTime = GETDATE() EXEC usp_SyncPersonnelData_LinkedServer SET @EndTime = GETDATE() PRINT '链接服务器耗时：' + CAST(DATEDIFF(ms, @StartTime, @EndTime) AS VARCHAR) + 'ms'
SET @StartTime = GETDATE() EXEC usp_SyncPersonnelData_OpenQuery SET @EndTime = GETDATE() PRINT 'OPENQUERY耗时：' + CAST(DATEDIFF(ms, @StartTime, @EndTime) AS VARCHAR) + 'ms'END
四、完整解决方案：带参数和日志
CREATE TABLE SyncLog ( LogID INT IDENTITY PRIMARY KEY, SyncTime DATETIME DEFAULT GETDATE(), SyncType NVARCHAR(50), RecordsAffected INT, Status NVARCHAR(20), ErrorMessage NVARCHAR(MAX))
CREATE PROCEDURE usp_SyncHRData @SyncType NVARCHAR(20) = 'ALL', @DebugMode BIT = 0 ASBEGIN SET NOCOUNT ON
DECLARE @StartTime DATETIME = GETDATE() DECLARE @RecordsAffected INT = 0 DECLARE @Status NVARCHAR(20) = 'SUCCESS' DECLARE @ErrorMessage NVARCHAR(MAX) = ''
BEGIN TRY IF NOT EXISTS (SELECT 1 FROM sys.servers WHERE name = 'RemoteHR') BEGIN RAISERROR('链接服务器RemoteHR不存在，请先创建', 16, 1) RETURN END
BEGIN TRANSACTION
SELECT CAST(工号 AS INT) AS PersonNo, 姓名 AS PersonName, 直间接 AS DutyType INTO #TempData FROM OPENQUERY(RemoteHR, ' SELECT 工号, 姓名, 直间接 FROM gwidb.dbo.out_TANXUEMEISYSTEMpawf WHERE 工号 IS NOT NULL ')
IF @DebugMode = 1 BEGIN SELECT * FROM #TempData ROLLBACK TRANSACTION RETURN END
IF @SyncType IN ('ALL', 'PERSONNEL') BEGIN UPDATE dp SET Workflag = 'N', LastUpdate = GETDATE() FROM syteline.SHATDB.dbo.DeptPerson dp LEFT JOIN #TempData td ON dp.PersonNo = td.PersonNo WHERE td.PersonNo IS NULL AND dp.Workflag = 'Y'
SET @RecordsAffected = @RecordsAffected + @@ROWCOUNT
UPDATE dp SET [Name] = td.PersonName, Duty = td.DutyType, Workflag = 'Y', LastUpdate = GETDATE() FROM syteline.SHATDB.dbo.DeptPerson dp INNER JOIN #TempData td ON dp.PersonNo = td.PersonNo
SET @RecordsAffected = @RecordsAffected + @@ROWCOUNT
INSERT INTO syteline.SHATDB.dbo.DeptPerson ( PersonNo, [Name], Workflag, Duty, CreateTime, LastUpdate ) SELECT td.PersonNo, td.PersonName, 'Y', td.DutyType, GETDATE(), GETDATE() FROM #TempData td WHERE NOT EXISTS ( SELECT 1 FROM syteline.SHATDB.dbo.DeptPerson dp WHERE dp.PersonNo = td.PersonNo )
SET @RecordsAffected = @RecordsAffected + @@ROWCOUNT END
INSERT INTO SyncLog (SyncTime, SyncType, RecordsAffected, Status, ErrorMessage) VALUES (GETDATE(), @SyncType, @RecordsAffected, @Status, @ErrorMessage)
COMMIT TRANSACTION
PRINT '同步完成！' PRINT '类型：' + @SyncType PRINT '耗时：' + CAST(DATEDIFF(second, @StartTime, GETDATE()) AS VARCHAR) + '秒' PRINT '处理记录数：' + CAST(@RecordsAffected AS VARCHAR)
END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
SET @Status = 'FAILED' SET @ErrorMessage = ERROR_MESSAGE()
INSERT INTO SyncLog (SyncTime, SyncType, RecordsAffected, Status, ErrorMessage) VALUES (GETDATE(), @SyncType, 0, @Status, @ErrorMessage)
PRINT '同步失败：' + @ErrorMessage END CATCH
IF OBJECT_ID('tempdb..#TempData') IS NOT NULL DROP TABLE #TempDataEND
六、使用示例
EXEC sp_addlinkedserver @server = 'RemoteHR', @provider = 'SQLOLEDB', @datasrc = '192.168.1.100'
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'RemoteHR', @rmtuser = 'sa', @rmtpassword = 'YourPassword'
EXEC usp_SyncHRData @DebugMode = 1
EXEC usp_SyncHRData
EXEC usp_SyncHRData @SyncType = 'PERSONNEL'
SELECT * FROM SyncLog ORDER BY SyncTime DESC
七、注意事项
| |
|---|
| 设置超时时间：SET LOCK_TIMEOUT 30000 |
| |
| |
| |
总结
OPENDATASOURCE：临时使用，配置简单
链接服务器：频繁使用，性能更好
OPENQUERY：复杂查询，可远程过滤
集合操作替代游标：性能提升10倍以上
阅读原文：原文链接
该文章在 2026/4/15 18:18:37 编辑过
400 186 1886
