Arithmetic overflow error (numeric to numeric) with MERGE: SQL Server bug?
sql, sql-server, sql-server-2008
Solution
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.
Problem
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; ```