Sql数据库利用linkserver和 CT[CHANGE_TRACKING]实现发布订阅

源服务器

初始化同步数据表

SELECT * INTO 【用于同步数据的表名】
FROM (
SELECT top 0
CT.SYS_CHANGE_VERSION,
CT.SYS_CHANGE_OPERATION,
CT.【同步数据表的主键ID】
FROM CHANGETABLE(CHANGES 源数据表名, 0) AS CT ) t

创建获取同步数据存储过程

TRUNCATE TABLE 【用于同步数据的表名】

INSERT INTO 【用于同步数据的表名】
(SYS_CHANGE_VERSION,SYS_CHANGE_OPERATION,【同步数据表的主键ID】)

SELECT
CT.SYS_CHANGE_VERSION,
CT.SYS_CHANGE_OPERATION,
CT.【同步数据表的主键ID】
FROM CHANGETABLE(CHANGES dbo.源数据表名, @last_sync_version) AS CT

订阅服务器

同步作业
创建同步记录表SyncTable

DECLARE @currentVersion BIGINT,@maxVersion bigInt
DECLARE @tableName nvarchar(100)
SET @tableName=''同步表名''
SELECT @currentVersion=SyncVersion FROM [dbo].[SyncTable] WHERE @tableName=tableName AND IsInitOver=1
---判断是否已经初始化完成
IF @currentVersion IS NOT null
BEGIN
EXEC xiaoben.SchoolBasic.dbo.[GetSyncData2] ''同步表名'',@currentVersion【最新版本】
DECLARE @insertCount INT,@updateCount INT,@deleteCount INT,@ChangeCount INT
SELECT @ChangeCount=COUNT(1),
@insertCount=ISNULL(SUM(CASE WHEN SYS_CHANGE_OPERATION=''I'' THEN 1 ELSE 0 end),0),
@deleteCount=ISNULL(SUM(CASE WHEN SYS_CHANGE_OPERATION=''D'' THEN 1 ELSE 0 end),0),
@updateCount=ISNULL(SUM(CASE WHEN SYS_CHANGE_OPERATION=''U'' THEN 1 ELSE 0 end),0)
FROM xiaoben.SchoolBasic.dbo.sync_tempSCHB_Account_UserInfo2
---开始批量插入
IF (@ChangeCount>0)
BEGIN

IF (@insertCount>0)
BEGIN
PRINT ''开始新增''
SET IDENTITY_INSERT [表名] ON

---新增先删除目标库存在的ID数据
INSERT INTO [dbo].[表名]
(【列名】
)
SELECT
t1.[列名]

FROM 【linkserver的名称】.[数据库的名称].dbo.[同步数据视图]  t1
WHERE t1.SYS_CHANGE_OPERATION=''I'' AND NOT EXISTS (SELECT [UserInfoID] FROM [dbo].[SCHB_Account_UserInfo] ttt WHERE ttt.[UserInfoID]=t1.[UserInfoID])

SET IDENTITY_INSERT [表名] OFF

END

IF @updateCount>0
BEGIN
---开始批量修改
PRINT ''开始修改''

UPDATE [表名] SET
[列名]=t.[列名]
,[列名]=t.[列名]

FROM (
select
t1.[列名]
,t1.[列名]

FROM 【linkserver的名称】.[数据库的名称].dbo.[同步数据视图] t1
WHERE t1.SYS_CHANGE_OPERATION=''U'') t where t.[表主键]=[表名].[表主键]
END

IF @deleteCount>0
BEGIN
---开始批量删除
PRINT ''开始删除''
DELETE FROM [表名] WHERE 【表主键】 IN(
SELECT 表主键 FROM 【linkserver的名称】.[数据库的名称].dbo.[同步数据视图]  WHERE SYS_CHANGE_OPERATION=''D''
)
END

INSERT INTO [dbo].[TableChangeLog]
([TableName]
,[SYS_CHANGE_VERSION]
,[SYS_CHANGE_OPERATION]
,[ID])
select
''表名''
,[SYS_CHANGE_VERSION]
,[SYS_CHANGE_OPERATION]
,[表主键] from 【linkserver的名称】.[数据库的名称].dbo.[同步数据视图] 

SELECT @currentVersion=MAX(SYS_CHANGE_VERSION) FROM 【linkserver的名称】.[数据库的名称].dbo.[同步数据视图] 
IF @currentVersion IS NOT NULL
BEGIN
PRINT ''更新版本''
UPDATE [dbo].[SyncTable] SET SyncVersion=@currentVersion WHERE @tableName=tableName AND IsInitOver=1
end
END
ELSE
BEGIN
PRINT ''无数据更新''
END
END