SQL Insert command error

Question

Create table Loan
(
    LoanID Int,
    LoanAmount Decimal(5,2)
)

Select * from Loan

Insert Into Loan(LoanID, LoanAmount)
Values (1, 1555), (2, 7500), (3, 2455), (4, 8630), (5, 5685)

I'm not able to insert values into the table with data type Decimal(5,2). It throws an error

Arithmetic overflow error converting int to data type numeric. The statement has been terminated.

If I increase the precision value it works.

Can someone help out?

Thank you

No need to be so restrictive. Disk space is cheap and SQL is fast. Leave some room for larger loans. For my loan systems, I've use the money type with no issues — John Cappelletti
– John Cappelletti, Commented May 26, 2016 at 16:18

SQLChao · Accepted Answer · 2016-05-26 16:21:20Z

2

There is not an issue. Precision is the total number of digits that will be stored and scale is number of digits to the right of the decimal. So in your example you have 2 digits to the right of the decimal and a total of 5 digits meaning you could only have 3 to the left.

https://msdn.microsoft.com/en-us/library/ms187746.aspx

Change it to Decimal(6,2) or even larger if you need to accommodate larger numbers.

edited May 26, 2016 at 16:21

answered May 26, 2016 at 16:14

SQLChao

7,8851 gold badge19 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Insert command error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related