MERGE的算术溢出错误（数字到数字）：SQL Server错误？

Question

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

当我尝试将数据UPSERT成TargetTable从SourceTable通过MERGE声明，我得到一个标准错误。

将数字转换为数据类型数字的算术溢出错误。

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

但是，奇怪的是：当我使用SELECT INTOonTargetTable创建测试表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

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