Arithmetic overflow error (numeric to numeric) with MERGE: SQL Server bug?

Question

I have a target table TargetTable that has a DECIMAL(20, 7) column. I also have a source table SourceTable that has a DECIMAL(20, 7) column, but the values in this table are all integers anyway.

When I try to upsert data into TargetTable from SourceTable via a MERGE statement, I get a standard error.

Arithmetic overflow error converting numeric to data type numeric.

I don't really understand why this would happen since both data types are identical.

The weird thing is this, though: when I use a SELECT INTO on TargetTable to create a test table TestTable, then change the target of the merge to TestTable, the upsert completes. I also don't understand why this would be the case, since TargetTable and TestTable are for the most part identical.

Has anyone encountered this before, is this a bug, or is there some obscure nuance of SQL Server that I'm missing?

Sample Code:

Fails:

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;

Succeeds:

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;

Also, if this sounds more appropriate for dba.stackexchange.com than SO, please weigh in. — Peter Majeed
@Lamak: I didn't think posting the code would be necessary, since this seems like something inconsistent more in principle than in specifics, but I will post the code. — Peter Majeed
Why don't you just try to use SET NUMERIC_ROUNDABORT Off and see what results are getting inserted into target table .This might give you an idea whether there is any loss of data — praveen
Do you have a trigger on TargetTable that may be causing this error? — Lamak

Lamak Lamak · Accepted Answer · 2012-06-07T15:16:52

Your code seems to have no error at all. Furthermore, the fact that it works fine when you create another table as the target, using the same structure and data that the original table shows that the error is not there. The most likely reason is that you have a trigger on your original table that is executing a query that fails, and that is the error you are seeing.

Arithmetic overflow error (numeric to numeric) with MERGE: SQL Server bug?

2 Answers