Arithmetic overflow error converting numeric to data type numeric

Solution 1:

My guess is that you're trying to squeeze a number greater than 99999.99 into your decimal fields. Changing it to (8,3) isn't going to do anything if it's greater than 99999.999 - you need to increase the number of digits before the decimal. You can do this by increasing the precision (which is the total number of digits before and after the decimal). You can leave the scale the same unless you need to alter how many decimal places to store. Try decimal(9,2) or decimal(10,2) or whatever.

You can test this by commenting out the insert #temp and see what numbers the select statement is giving you and see if they are bigger than your column can handle.

Solution 2:

I feel I need to clarify one very important thing, for others (like my co-worker) who came across this thread and got the wrong information.

The answer given ("Try decimal(9,2) or decimal(10,2) or whatever.") is correct, but the reason ("increase the number of digits before the decimal") is wrong.

decimal(p,s) and numeric(p,s) both specify a Precision and a Scale. The "precision" is not the number of digits to the left of the decimal, but instead is the total precision of the number.

For example: decimal(2,1) covers 0.0 to 9.9, because the precision is 2 digits (00 to 99) and the scale is 1. decimal(4,1) covers 000.0 to 999.9 decimal(4,2) covers 00.00 to 99.99 decimal(4,3) covers 0.000 to 9.999