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.消息245，级别16，状态1，行1转换将varchar值'NULL'转换为数据类型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?
我理解错误，但想知道为什么错误消息不识别导致问题的列。有没有一种简单的方法可以找到哪个列包含顽皮的null，或者这只是一种让我看起来像工作效率高的工作，而我真的只花了30分钟看着一个巨大的结果集而没有到达任何地方？
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!
编辑：感谢帮助人员，但没有人真正回答这个问题。是否所有RDBMS都会发出类似的错误消息或者更有帮助？它的2012年...可能数千列的试验和错误应该已经死了！
3 个解决方案
#1
4
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:
我会看看你如何填充临时表。您似乎获得的值为“null”而不是NULL。如果此数据来自Excel文件，则这是一个常见问题。我通常通过这种方式更新数据：
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')
#2
1
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.
它不应该花30分钟来确定null的位置。你只有这么多列。刚开始从#temp中选择WHERE col1 IS NULL，然后是WHERE col2。
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).
如果#temp有一个VARCHAR列，你试图将其放入INT列然后转换它。如果有NULL，您可能希望使用CAST（ISNULL（VarCharColumn，'0'）AS INT）处理它们。如果INT列允许NULLS，那么只需要转换为INT就足够了（只要所有值都是NULL或有效的int）。
If you write your INSERT with a little bit more care then you should be able to get the results you want.
如果你用更小心的方式编写INSERT，那么你应该能够得到你想要的结果。
#3
0
You're going to need some trial and error as @Jeremy pointed out. But you can winnow down the choices.
@Jeremy指出，你需要一些试验和错误。但你可以放下选择。
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
错误消息表明varchar列中的问题是NULL。您可以将搜索限制为#temp中的varchar列：select * from #temp其中col1为null或col3为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.
其次，当数据库引擎尝试将null varchar值转换为非null的整数时，也会出现问题。比较两个表的定义，以查看#temp中的varchar与另一个表中的非整数匹配的位置。
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.
然而，这是可疑的。为什么要尝试将文本转换为数字？如果那是你真正想做的事情，你可能需要从文本到数字的显式转换。
#1
4
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:
我会看看你如何填充临时表。您似乎获得的值为“null”而不是NULL。如果此数据来自Excel文件，则这是一个常见问题。我通常通过这种方式更新数据：
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')
#2
1
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.
它不应该花30分钟来确定null的位置。你只有这么多列。刚开始从#temp中选择WHERE col1 IS NULL，然后是WHERE col2。
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).
如果#temp有一个VARCHAR列，你试图将其放入INT列然后转换它。如果有NULL，您可能希望使用CAST（ISNULL（VarCharColumn，'0'）AS INT）处理它们。如果INT列允许NULLS，那么只需要转换为INT就足够了（只要所有值都是NULL或有效的int）。
If you write your INSERT with a little bit more care then you should be able to get the results you want.
如果你用更小心的方式编写INSERT，那么你应该能够得到你想要的结果。
#3
0
You're going to need some trial and error as @Jeremy pointed out. But you can winnow down the choices.
@Jeremy指出，你需要一些试验和错误。但你可以放下选择。
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
错误消息表明varchar列中的问题是NULL。您可以将搜索限制为#temp中的varchar列：select * from #temp其中col1为null或col3为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.
其次，当数据库引擎尝试将null varchar值转换为非null的整数时，也会出现问题。比较两个表的定义，以查看#temp中的varchar与另一个表中的非整数匹配的位置。
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.
然而，这是可疑的。为什么要尝试将文本转换为数字？如果那是你真正想做的事情，你可能需要从文本到数字的显式转换。