How to identify the culprit in "Error converting data type varchar to numeric"?

Question

An insert statement with a large number of values returns 'Error converting data type varchar to numeric.'

How can I find which value actually triggers the error?

MS SQL Server 2008 is used.

Answer 1

you can use the function ISNUMERIC()

select * from table
where isnumeric(column) = 0

or

declare @v varchar(100)
select @v = 'abd'

select ISNUMERIC(@v)

Also take a look at this IsNumeric, IsInt, IsNumber post to help you further with these kind of problems