Why can't SQL Server tell me which column is causing the error

Question

I'm running a pretty standard

INSERT INTO [table] (col1, col2, ...coln)
select * from #temp

and I'm getting the following error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'NULL' to data type int.

I understand the error, but want to know why the column that is causing issue isn't identified by the error message. Is there an easy way to find which column contains the naughty null or is this just a ploy to make me look like I'm being productive at work while I really just spent 30 minutes looking at a huge result set without getting anywhere?

Edit: Thanks for the help guys, but no one really answered the question. Do all RDBMS's spew out similar error messages are or some more helpful? Its 2012...trial and error over possibly thousands of columns should be dead!

Answer 1

I would look at how you populate the temp table. You appear to be getting a value of 'null' not NULL. If this data is coming from a Excel file, this is a common problem. I usually clease the data first by updating this way:

Update #temp
set field1 = NULL
where field1 = 'NULL'

If you want to do all in the same update command, then

Update #temp
set field1 = NULLIF(field1, 'NULL')
, field2 = NULLIF(field2, 'NULL')
, field3 = NULLIF(field3, 'NULL')

Answer 2

It shouldn't take you 30 minutes to figure out where the null is. You only have so many columns. Just start selecting from #temp WHERE col1 IS NULL, then WHERE col2 is.

If #temp has a VARCHAR column you're trying to put into in INT column then cast it. If there are NULLs you might want to handle them with an CAST(ISNULL(VarCharColumn, '0') AS INT) or something. If an INT column allows NULLS, then just the cast to INT should be enough (as long as all the values are NULL or a valid int).

If you write your INSERT with a little bit more care then you should be able to get the results you want.