算术溢出错误与合并（数字到数字）：SQL Server的错误吗？(Arithmetic overfl

我有一个目标表TargetTable具有DECIMAL(20, 7)柱。我也有一个源表SourceTable具有DECIMAL(20, 7)列，但此表中的值都是整数反正。

当我尝试UPSERT数据到TargetTable从SourceTable通过MERGE语句，我得到一个标准错误。

算术溢出错误转换数字数据类型数值。

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

奇怪的是这一点，虽然：当我使用SELECT INTO上TargetTable创建一个测试表TestTable ，然后更改合并到的目标TestTable ，该更新插入完成。我也弄不明白为什么会是这样，因为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;

你的代码似乎没有错误可言。而且，事实上，当你创建另一个表作为目标，使用相同的结构和数据的原始表显示错误是不存在的，它工作正常。最可能的原因是，你必须对正在执行一个失败的查询您的原始表的触发器，这是您所看到的错误。