为什么 SQL Server 不能告诉我哪个列导致了错误 [英] Why can't SQL Server tell me which column is causing the error

问题描述

我正在运行一个非常标准的

INSERT INTO [table] (col1, col2, ...coln)从 #temp 中选择 *

我收到以下错误:

<块引用>

消息 245，级别 16，状态 1，第 1 行
将 varchar 值NULL"转换为数据类型 int 时转换失败.

我理解错误，但想知道为什么错误消息没有识别导致问题的列.有没有一种简单的方法可以找到哪一列包含顽皮的空值，或者这只是一种让我看起来工作效率很高的策略，而我真的只花了 30 分钟查看一个巨大的结果集却一无所获?

感谢各位的帮助，但没有人真正回答这个问题.是否所有 RDBMS 都喷出类似的错误消息或一些更有帮助?它的 2012 年......对可能数千列的反复试验应该已经死了！

解决方案

我会看看你如何填充临时表.您似乎获得了null"而不是 NULL 的值.如果此数据来自 Excel 文件，则这是一个常见问题.我通常先通过这种方式更新清除数据:

更新#temp设置字段 1 = NULL其中字段 1 = 'NULL'

如果您想在同一个更新命令中完成所有操作，那么

更新#tempset field1 = NULLIF(field1, 'NULL'), field2 = NULLIF(field2, 'NULL'), field3 = NULLIF(field3, 'NULL')

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!

解决方案

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

这篇关于为什么 SQL Server 不能告诉我哪个列导致了错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！