Arithmetic overflow error converting float to data type numeric. 1

[hvisionexpo]

Is there a way to TURN-OFF this error "Arithmetic overflow error converting float to data type numeric."

so I an perform insert from a float column to numberic.

I do not care if it gets truncated.
If not, Is there an alternate solution to insert the values from one table to another with different column types?

THANKS

 

[SQLBill]

You could do this....

When SET ARITHABORT is OFF and an INSERT, UPDATE, or DELETE statement encounters an arithmetic error, SQL Server inserts or updates a NULL value. If the target column is not nullable, the insert or update action fails and the user receives an error.

But I don't think that will really solve your problem. Give it try.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

Have you tried Declaring a value and then set it to the truncated value, then do your Insert statement with the Variable instead of with the actual convert statement?

For example:

Declare @MyNumeric

Set @MyNumeric = Convert(numeric, table.MyFloat)

Insert into Table2 (blah, blah, Numeric)
(Select blah, blah, @MyNumeric)

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

BTW, I just tested this and it worked, so it should work for your insert statement.

Declare @MyNumeric numeric(5, 3)

Set @MyNumeric = Convert(numeric(5,3), 10.5555566678)

Select @MyNumeric

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"