sql - MERGE : SQL Server bug? 的算术溢出错误(数字到数字)

Question

我有一个目标表 TargetTable 有一个 DECIMAL(20, 7) 列。我还有一个源表 SourceTable 有一个 DECIMAL(20, 7) 列，但是这个表中的值无论如何都是整数。

当我尝试通过 MERGE 语句将数据从 SourceTable 插入到 TargetTable 时，出现标准错误。

Arithmetic overflow error converting numeric to data type numeric.

我真的不明白为什么会发生这种情况，因为这两种数据类型是相同的。

不过，奇怪的是:当我在 TargetTable 上使用 SELECT INTO 创建测试表 TestTable 时，然后更改将目标合并到 TestTable，则 upsert 完成。我也不明白为什么会这样，因为 TargetTable 和 TestTable 大部分是相同的。

以前有没有人遇到过这种情况，这是一个错误，还是我遗漏了 SQL Server 的一些模糊的细微差别？

---

示例代码:

失败:

SET NUMERIC_ROUNDABORT Off
GO

MERGE
    TargetTable Target
USING
    (
        SELECT
            cast(forecast as DECIMAL(20, 7)) forecast
          ,[SegmentID]
          ,[Country]
          ,[Environment]
          ,[YearColumn]
          ,[ForecastYear]
          ,[Criterion]
        FROM
            SourceTable
    ) Source
    ON
    (
        Target.SegmentID = Source.SegmentID
        AND Target.Country = Source.Country
        AND Target.Environment = Source.Environment
        AND Target.YearColumn = Source.YearColumn
        AND Target.ForecastYear = Source.ForecastYear
        AND Target.Criterion = Source.Criterion
    )
WHEN NOT MATCHED BY TARGET AND Source.Forecast <> 0 AND Source.Forecast IS NOT NULL THEN
    INSERT (SegmentID, Country, Environment, YearColumn, Forecast, ForecastYear, Criterion)
    VALUES (Source.SegmentID, Source.Country, Source.Environment, Source.YearColumn, Source.Forecast, Source.ForecastYear, Source.Criterion)
WHEN MATCHED AND (Source.Forecast = 0 OR Source.Forecast IS NULL) THEN
    DELETE
WHEN MATCHED AND Source.Forecast <> Target.Forecast THEN
    UPDATE SET Target.Forecast = Source.Forecast;

成功:

SELECT
    *
INTO
    TestTable
FROM
    TargetTable
GO

SET NUMERIC_ROUNDABORT Off
GO

MERGE
    TestTable Target
USING
    (
        SELECT
            cast(forecast as DECIMAL(20, 7)) forecast
          ,[SegmentID]
          ,[Country]
          ,[Environment]
          ,[YearColumn]
          ,[ForecastYear]
          ,[Criterion]
        FROM
            SourceTable
    ) Source
    ON
    (
        Target.SegmentID = Source.SegmentID
        AND Target.Country = Source.Country
        AND Target.Environment = Source.Environment
        AND Target.YearColumn = Source.YearColumn
        AND Target.ForecastYear = Source.ForecastYear
        AND Target.Criterion = Source.Criterion
    )
WHEN NOT MATCHED BY TARGET AND Source.Forecast <> 0 AND Source.Forecast IS NOT NULL THEN
    INSERT (SegmentID, Country, Environment, YearColumn, Forecast, ForecastYear, Criterion)
    VALUES (Source.SegmentID, Source.Country, Source.Environment, Source.YearColumn, Source.Forecast, Source.ForecastYear, Source.Criterion)
WHEN MATCHED AND (Source.Forecast = 0 OR Source.Forecast IS NULL) THEN
    DELETE
WHEN MATCHED AND Source.Forecast <> Target.Forecast THEN
    UPDATE SET Target.Forecast = Source.Forecast;

Answer 1

您的代码似乎完全没有错误。此外，当您使用与原始表相同的结构和数据创建另一个表作为目标时，它可以正常工作，这表明错误不存在。最可能的原因是您的原始表上有一个触发器正在执行失败的查询，这就是您看到的错误。