Error converting data type varchar to numeric.

Question

Error converting data type varchar to numeric.

kajalchatarjee 5928

SSC-Addicted

Points: 406
More actions
December 21, 2011 at 8:09 am

#250245

Hello All,
I am new to sql server. I am getting following error when I am trying to insert data into target table.
"Error converting data type varchar to numeric."
so I tried to debug by inserting few number of rows to find out if there is a problem in data. But when I tried to divide data and insert the data, it is inserting and not giving any error. But when I try to insert large volume of data it is giving above error.
This Job is running from 3 months and this is first time it is giving this error.
Please help me. I am not able to understand where the error is.
Thanks,
Kajal

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

toddasd SSCertifiable Points: 6298 More actions · Answer 1

kajalchatarjee 5928 (12/21/2011)
Hello All,
I am new to sql server. I am getting following error when I am trying to insert data into target table.
"Error converting data type varchar to numeric."
so I tried to debug by inserting few number of rows to find out if there is a problem in data. But when I tried to divide data and insert the data, it is inserting and not giving any error. But when I try to insert large volume of data it is giving above error.
This Job is running from 3 months and this is first time it is giving this error.
Please help me. I am not able to understand where the error is.
Thanks,
Kajal

Seems like you are most of the way there. There is some varchar data that normally implicitly converts to numeric, but now you have some data mixed in there that cannot convert. A basic way to find that bad data is like this:

SELECT * FROM Source_Table WHERE ISNUMERIC(Source_Column) = 0;

but only you can know what column to test.

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

kajalchatarjee 5928 SSC-Addicted Points: 406 More actions · Answer 2

Thanks for the response.

I did check the data but there was no error... because , when I take two sample sets of data and try inserting together it is giving that error.

But when I am individually inserting those two sample data set it is inserting into target table. If it is data issue then it should not insert individually also right?

and also I didn't make any changes in the code from the time it is in production.

Please correct me if I am thinking in a wrong way.

Thanks,

Kajal

toddasd SSCertifiable Points: 6298 More actions · Answer 3

kajalchatarjee 5928 (12/21/2011)
...
I did check the data but there was no error... because , when I take two sample sets of data and try inserting together it is giving that error.

Can you show an example of inserting together versus individually?

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

kajalchatarjee 5928 SSC-Addicted Points: 406 More actions · Answer 4

for example :

emp1 and emp2 are two sets of data. when I try to insert emp1 and emp2 individually as below it inserts all the rows successfully.

select * from table_name

where X = 'emp1'

select * from table_name

where X ='emp2'

but when I try to insert data for both together like

select *

from table_name

where X in(emp1, emp2)

it is giving me error " Error converting data type varchar to numeric."

in the code which I am running I am converting some columns from varchar to numeric using isnumeric and case statement like below :

case when isnumeric([PSREC24] ) = 0 then NULL else ([PSREC24] ) end

Thanks,

Kajal

toddasd SSCertifiable Points: 6298 More actions · Answer 5

It's bad data. You have a character that passes for numeric but cannot be inserted into a numeric field. Examine:

select isnumeric('$00')--returns 1, is numeric according to the function

select cast('$00' as numeric)--Error converting data type varchar to numeric.

Edit: formatting code box

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

toddasd SSCertifiable Points: 6298 More actions · Answer 6

Also, this article can give you some insight on how isnumeric works and what pitfalls to avoid.

http://www.sqlservercentral.com/articles/IsNumeric/71512/

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

kajalchatarjee 5928 SSC-Addicted Points: 406 More actions · Answer 7

kajalchatarjee 5928

SSC-Addicted

Points: 406

December 21, 2011 at 10:37 am

#1424365

thank you.. I will try it and get back