SQL Server 2012替代MERGE实现数据更新插入方案咨询

针对SQL Server 2012不兼容原有MERGE脚本的问题，提供两种可靠的替代方案，均能实现"更新已有记录、插入新记录"的逻辑：

方案一：修正MERGE语法适配SQL Server 2012 #

SQL Server 2012对MERGE的语法约束更严格，需确保目标表有唯一索引匹配关联列，同时优化语法细节：

步骤1：确保目标表存在唯一组合索引（若未创建） #

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_Alert_HourlyCounts_DateHour' AND object_id = OBJECT_ID('Alert_HourlyCounts'))
BEGIN
    CREATE UNIQUE NONCLUSTERED INDEX IX_Alert_HourlyCounts_DateHour
    ON Alert_HourlyCounts (AlertDate, AlertHour);
END

步骤2：兼容版MERGE脚本 #

CREATE PROCEDURE dbo.StatAlertHourlyCounts
    @TargetDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    MERGE Alert_HourlyCounts AS Target
    USING (
        SELECT 
            CONVERT(DATE, AlertTime) AS AlertDate,
            DATEPART(HOUR, AlertTime) AS AlertHour,
            COUNT_BIG(*) AS EventCount -- 避免大计数溢出，替代COUNT(*)
        FROM Alerts
        WHERE CONVERT(DATE, AlertTime) = @TargetDate
        GROUP BY CONVERT(DATE, AlertTime), DATEPART(HOUR, AlertTime)
    ) AS Source
    ON Target.AlertDate = Source.AlertDate 
       AND Target.AlertHour = Source.AlertHour
    WHEN MATCHED THEN
        UPDATE SET Target.EventCount = Source.EventCount
    WHEN NOT MATCHED BY TARGET THEN -- 明确指定匹配方向，避免歧义
        INSERT (AlertDate, AlertHour, EventCount)
        VALUES (Source.AlertDate, Source.AlertHour, Source.EventCount);
END

方案二：传统UPDATE+INSERT（更稳定的UPSERT） #

如果MERGE仍出现问题，推荐使用这种分步骤的方式，兼容性更好，逻辑更直观：

脚本实现（含临时表优化） #

CREATE PROCEDURE dbo.StatAlertHourlyCounts
    @TargetDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 将统计结果存入临时表，避免重复扫描源表
    SELECT 
        CONVERT(DATE, AlertTime) AS AlertDate,
        DATEPART(HOUR, AlertTime) AS AlertHour,
        COUNT_BIG(*) AS EventCount
    INTO #HourlyStats
    FROM Alerts
    WHERE CONVERT(DATE, AlertTime) = @TargetDate
    GROUP BY CONVERT(DATE, AlertTime), DATEPART(HOUR, AlertTime);

    -- 更新已有日期小时的统计数
    UPDATE Target
    SET Target.EventCount = Source.EventCount
    FROM Alert_HourlyCounts AS Target
    INNER JOIN #HourlyStats AS Source
    ON Target.AlertDate = Source.AlertDate 
       AND Target.AlertHour = Source.AlertHour;

    -- 插入新的日期小时统计记录
    INSERT INTO Alert_HourlyCounts (AlertDate, AlertHour, EventCount)
    SELECT AlertDate, AlertHour, EventCount
    FROM #HourlyStats AS Source
    LEFT JOIN Alert_HourlyCounts AS Target
    ON Target.AlertDate = Source.AlertDate 
       AND Target.AlertHour = Source.AlertHour
    WHERE Target.AlertDate IS NULL;

    DROP TABLE #HourlyStats;
END

关键注意事项 #

• 确保Alert_HourlyCounts表的AlertDate（DATE类型）、AlertHour（INT类型）、EventCount（BIGINT/INT类型）与统计逻辑匹配。
• 若原有脚本使用了CAST(AlertTime AS DATE)，替换为CONVERT(DATE, AlertTime)可避免旧版本的潜在类型转换问题。
• 优先测试方案二，因为SQL Server 2012的MERGE存在部分已知bug，分步骤更新插入更稳定。

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