SQL存储过程合并生产与预测表时日期重叠致数据重复的解决方法

编写两个存储过程先后向[dbo].[FORECAST_PRODUCTION]表插入实际生产数据、预测数据，预期执行顺序为先写入生产数据，再写入预测数据。当前单独执行生产数据插入逻辑运行完全正常，但执行预测数据插入时，若生产数据与预测数据存在同月份日期重合，会重复插入对应生产数据。此前尝试使用DISTINCT关键字去重、将两段逻辑合并为单个存储过程，但两张源表的日期字段分别为P_Date和Outdate，无法正确编写合并逻辑，目前仅能实现预测数据在生产数据之后插入，日期重合场景下的生产数据重复问题始终无法解决。

生产数据插入逻辑 #

INSERT INTO [dbo].[FORECAST_PRODUCTION]
    (PROPNUM,
        LEASE,
        OPERATOR,
        API,
        OUT_DATE,
    OIL,
    GAS)
    
SELECT DISTINCT     
    ac.PROPNUM,
       ac.LEASE,
        ac.OPERATOR,
        ac.API,
    mp.P_DATE as OUT_DATE,
    mp.OIL,
    mp.GAS
FROM [AC_PROPERTY] ac
INNER JOIN AC_PRODUCT mp
ON ac.PROPNUM = mp.PROPNUM
WHERE mp.P_DATE >= '1/31/2016' AND ac.ARLP_EVAL = 'YES' AND ac.PROPNUM ='V3UJ28DE65'
END

;

预测数据插入逻辑 #

INSERT INTO [dbo].[FORECAST_PRODUCTION]
    (PROPNUM,
        LEASE,
        OPERATOR,
        API,
    OUT_DATE,
    GROSS_OIL,
    GROSS_GAS,
    SCENARIO)
    
SELECT DISTINCT
    ac.PROPNUM,
        ac.LEASE,
        ac.OPERATOR,
         ac.API,
     fcst.OUTDATE,
     ROUND(fcst.GROSS_OIL,0),
     ROUND(fcst.GROSS_GAS,0),
     fcst.SCENARIO
FROM [AC_PROPERTY] ac
INNER JOIN AC_MONTHLY fcst
ON ac.PROPNUM = fcst.PROPNUM
WHERE fcst.SCENARIO = 'ARLP_STRIP' AND ac.PROPNUM = 'V3UJ28DE65' AND FCST.OUTDATE >2/28/2022    

END;

• 预测插入逻辑的日期过滤条件存在语法错误：FCST.OUTDATE >2/28/2022未给日期值加单引号，SQL Server会将2/28/2022识别为算术表达式（计算结果约为0.000035，对应datetime类型的日期接近1900-01-01），导致日期过滤完全失效，所有历史日期的预测数据都会被查询出来，和已插入的生产数据日期重合。
• DISTINCT仅能对单次SELECT查询的结果集做去重，无法校验目标表中已经存在的历史数据，因此无法解决跨两次插入操作产生的重复记录。

方案1：保留两个独立存储过程，新增重复数据校验 #

修改预测数据插入逻辑，修正日期格式问题，新增NOT EXISTS判断，排除目标表中已经存在的对应井号、相同日期的生产数据，避免重复插入。
修改后的预测插入代码如下：

INSERT INTO [dbo].[FORECAST_PRODUCTION]
    (PROPNUM,
        LEASE,
        OPERATOR,
        API,
    OUT_DATE,
    GROSS_OIL,
    GROSS_GAS,
    SCENARIO)
    
SELECT DISTINCT
    ac.PROPNUM,
        ac.LEASE,
        ac.OPERATOR,
         ac.API,
     fcst.OUTDATE,
     ROUND(fcst.GROSS_OIL,0),
     ROUND(fcst.GROSS_GAS,0),
     fcst.SCENARIO
FROM [AC_PROPERTY] ac
INNER JOIN AC_MONTHLY fcst
ON ac.PROPNUM = fcst.PROPNUM
WHERE fcst.SCENARIO = 'ARLP_STRIP' 
  AND ac.PROPNUM = 'V3UJ28DE65' 
  -- 修正日期格式，统一用YYYY-MM-DD格式加单引号，避免识别错误
  AND fcst.OUTDATE > '2022-02-28'
  -- 排除已存在的生产数据（SCENARIO为空代表是实际生产数据）
  AND NOT EXISTS (
      SELECT 1 
      FROM [dbo].[FORECAST_PRODUCTION] t
      WHERE t.PROPNUM = ac.PROPNUM
        AND t.OUT_DATE = fcst.OUTDATE
        AND t.SCENARIO IS NULL
  )

方案2：合并为单个存储过程，从根源避免日期重合 #

将两段逻辑合并，先插入生产数据，再仅插入晚于该井最大生产数据日期的预测数据，通过UNION ALL一次性写入，不需要事后做重复校验，同时支持参数化传入井号，避免硬编码。
合并后的存储过程代码如下：

CREATE PROCEDURE [dbo].[SP_LOAD_FORECAST_PRODUCTION]
    @PROPNUM VARCHAR(32) = 'V3UJ28DE65'
AS
BEGIN
    SET NOCOUNT ON;
    -- 先清理该井号的历史数据，避免多次执行产生重复
    DELETE FROM [dbo].[FORECAST_PRODUCTION] WHERE PROPNUM = @PROPNUM;

    INSERT INTO [dbo].[FORECAST_PRODUCTION]
        (PROPNUM,
        LEASE,
        OPERATOR,
        API,
        OUT_DATE,
        OIL,
        GAS,
        GROSS_OIL,
        GROSS_GAS,
        SCENARIO)
    
    -- 第一部分：实际生产数据
    SELECT 
        ac.PROPNUM,
        ac.LEASE,
        ac.OPERATOR,
        ac.API,
        mp.P_DATE as OUT_DATE,
        mp.OIL,
        mp.GAS,
        NULL AS GROSS_OIL,
        NULL AS GROSS_GAS,
        NULL AS SCENARIO
    FROM [AC_PROPERTY] ac
    INNER JOIN AC_PRODUCT mp
        ON ac.PROPNUM = mp.PROPNUM
    WHERE mp.P_DATE >= '2016-01-31' 
      AND ac.ARLP_EVAL = 'YES' 
      AND ac.PROPNUM = @PROPNUM

    UNION ALL

    -- 第二部分：预测数据，仅取晚于该井最大生产日期的部分
    SELECT 
        ac.PROPNUM,
        ac.LEASE,
        ac.OPERATOR,
        ac.API,
        fcst.OUTDATE as OUT_DATE,
        NULL AS OIL,
        NULL AS GAS,
        ROUND(fcst.GROSS_OIL,0),
        ROUND(fcst.GROSS_GAS,0),
        fcst.SCENARIO
    FROM [AC_PROPERTY] ac
    INNER JOIN AC_MONTHLY fcst
        ON ac.PROPNUM = fcst.PROPNUM
    WHERE fcst.SCENARIO = 'ARLP_STRIP' 
      AND ac.PROPNUM = @PROPNUM 
      -- 动态获取生产数据最大日期，彻底避免日期重合
      AND fcst.OUTDATE > (
          SELECT MAX(mp.P_DATE) 
          FROM AC_PRODUCT mp 
          WHERE mp.PROPNUM = @PROPNUM
      )
END
GO

• 日期常量统一使用'YYYY-MM-DD'格式，添加单引号，避免SQL Server不同区域配置下的日期识别错误。
• 为[dbo].[FORECAST_PRODUCTION]表添加联合唯一约束(PROPNUM, OUT_DATE, SCENARIO)，从数据库层面阻止重复数据插入，即使代码逻辑存在漏洞也不会产生脏数据。

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