"Error converting data type varchar to numeric." - What column?

Question

I have a huge INSERT-statement with 200 columns and suddendly I get the dreaded Error converting data type varchar to numeric. Is there somewhere I can see the actual column that contains the "varchar" value? I know I can remove one of the columns at a time until the error disappears, but it's very tedious.

Answer 1

Unfortunately, this error is a serious pain and there's no easy way to troubleshoot it. When I've encountered it in the past, I've always just had to comment out groups of columns until I find the culprit.

Another approach might be to use the ISNUMERIC() function in in T-SQL to try and find the culprit. Assuming every column in your destination table is numeric (adjust accordingly if it's not), you could try this:

SELECT *
  FROM SourceTable
 WHERE ISNUMERIC(Column1) = 0
    OR ISNUMERIC(Column2) = 0
    OR ISNUMERIC(Column3) = 0
    OR ISNUMERIC(Column4) = 0
    ...

This will expose the row that contains your non-numeric value, and should make it pretty clear which column it's in. I know it's tedious, but at least it helps you hunt down the actual value, in addition to the column that's causing trouble.

Answer 2

You don't specify SQL Server Version or number of rows.

For SQL2005+ adding the OUTPUT clause to the INSERT might help identify the rogue row in that it will output the inserted rows until it encounters an error so the next row is the one with the problem

DECLARE @Source TABLE
(
Col1 VARCHAR(10),
Col2 VARCHAR(10)
)

INSERT INTO @Source 
SELECT '1','1' UNION ALL
SELECT '2','2' UNION ALL
SELECT '3','3' UNION ALL
SELECT '4A','4' UNION ALL
SELECT '5','5' 


DECLARE @Destination TABLE
(
Col1 INT,
Col2 VARCHAR(10)
)

INSERT INTO @Destination 
OUTPUT inserted.*
SELECT * 
FROM @Source

Returns

    (5 row(s) affected)
    Col1        Col2
    ----------- ----------
    1           1
    2           2
    3           3
    Msg 245, Level 16, State 1, Line 23
    Conversion failed when converting the varchar value '4A' to data type int.