请求编写同步更新Watermark表对应记录的SQL存储过程

嘿，这需求我熟！给你整个简易又实用的存储过程方案，完美适配你的场景：

核心逻辑很简单：因为每张业务表的DateLoaded是统一的，我们只需要拿到目标表的这个值，然后同步到Watermarktable里对应记录就行。为了健壮性，用MERGE语句既可以更新已有记录，也能自动插入新业务表的记录（如果后续加新表的话）。

1. 单表同步存储过程 #

先写一个基础的存储过程，用来更新指定业务表的水印记录：

CREATE PROCEDURE UpdateWatermark
    @TableName NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- 存储目标表的最新DateLoaded值
    DECLARE @LatestDateLoaded DATETIME;
    -- 动态SQL获取表的DateLoaded（用QUOTENAME防止SQL注入）
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'SELECT @LatestDate = DateLoaded FROM ' + QUOTENAME(@TableName) + N' WHERE 1=1';
    
    -- 执行动态SQL并输出结果
    EXEC sp_executesql @SQL, N'@LatestDate DATETIME OUTPUT', @LatestDate = @LatestDateLoaded OUTPUT;

    -- 只有当表中有数据时才同步（避免空值更新）
    IF @LatestDateLoaded IS NOT NULL
    BEGIN
        -- MERGE同步：存在则更新，不存在则插入
        MERGE INTO Watermarktable AS Target
        USING (SELECT @TableName AS TableName, @LatestDateLoaded AS DateLoaded) AS Source
        ON Target.[Table Name] = Source.TableName
        WHEN MATCHED THEN
            UPDATE SET Target.DateLoaded = Source.DateLoaded
        WHEN NOT MATCHED THEN
            INSERT ([Table Name], DateLoaded)
            VALUES (Source.TableName, Source.DateLoaded);
    END
END
GO

使用方式 #

比如要更新TableA的水印，直接执行：

EXEC UpdateWatermark 'TableA';

2. 批量同步所有业务表 #

如果需要一次性更新所有业务表的水印，可以写个循环调用上面的存储过程：

DECLARE @TableName NVARCHAR(128);
-- 游标遍历所有业务表（这里手动指定，也可以用过滤条件排除Watermarktable）
DECLARE TableCursor CURSOR FOR
    SELECT name FROM sys.tables WHERE name IN ('TableA', 'TableB', 'TableC');

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC UpdateWatermark @TableName;
    FETCH NEXT FROM TableCursor INTO @TableName;
END

CLOSE TableCursor;
DEALLOCATE TableCursor;
GO

3. 自动触发同步（可选） #

如果想让业务表的DateLoaded更新时自动同步水印，可以给每张业务表加个触发器：

-- 给TableA创建触发器
CREATE TRIGGER Trigger_UpdateTableAWatermark
ON TableA
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    -- 只有当DateLoaded列被修改时才触发
    IF UPDATE(DateLoaded)
    BEGIN
        EXEC UpdateWatermark 'TableA';
    END
END
GO

-- 同理给TableB、TableC创建相同逻辑的触发器

这样以后只要业务表的DateLoaded更新，水印表就会自动同步，完全不用手动操作！

内容的提问来源于stack exchange，提问作者Ankit Kumar