CODEWITHSUNDEEP
sqlsql-server
woggles
woggles

Reputation: 7444

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

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!

Upvotes: 12

Views: 3516

Answers (4)

Marc Guvenc
Marc Guvenc

Reputation: 81

If you run these two statements before your query you will see null values on those columns in the results set:

SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF

Upvotes: 0

HLGEM
HLGEM

Reputation: 96572

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')

Upvotes: 4

Barry Brown
Barry Brown

Reputation: 20604

You're going to need some trial and error as @Jeremy pointed out. But you can winnow down the choices.

The error message says that the problem is a NULL in a varchar column. You can restrict your searching to just the varchar columns in #temp: select * from #temp where col1 is null or col3 is null

Second, the problem is also happening when the database engine tries to convert a null varchar value to an integer not null. Compare the definitions of both tables to see where a varchar in #temp matches up with an integer not null in the other table.

This, however, is suspicious. Why are you trying to convert text to numbers? If that's what you really want to do, you will probably need an explicit cast from textual to numeric.

Upvotes: 0

Jeremy Pridemore
Jeremy Pridemore

Reputation: 1995

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.

Upvotes: 1

Related Questions