When I insert records into a long table,I am getting the error "Conversion failed when converting the varchar value 'NULL' to data type int" How can I determine which column errors out?
当我将记录插入长表时，我收到错误“将varchar值'NULL'转换为数据类型int时转换失败”如何确定哪些列错误？
The table has many fields and millions of records. Each iteration takes 10 minutes to bomb when I try to insert "NULL" string into integer column somewhere. I thought SQL server can tell me exact name of the column :(
该表有许多字段和数百万条记录。当我尝试在某处将“NULL”字符串插入整数列时，每次迭代需要10分钟才能完成。我以为SQL服务器可以告诉我列的确切名称:(
4 个解决方案
#1
12
If the value is truly NULL, there wouldn't be a conversion error. However, you have a string = "NULL" then you would get this error.
如果该值确实为NULL，则不会出现转换错误。但是，你有一个string =“NULL”然后你会得到这个错误。
What you could do is...
你能做的是......
NullIf(YourValueHere, 'NULL')
NullIf returns the value of the first parameter if it is not the same as the second parameter. If the parameters are the same, NullIf will return NULL.
如果第一个参数与第二个参数不同，则返回NullIf的值。如果参数相同，NullIf将返回NULL。
Ex:
例如：
Select NullIf('Any Value', 'NULL')
Select NullIf('null','null')
The first will return 'Any Value' and the second will return NULL (not 'null')
第一个将返回'Any Value'，第二个将返回NULL（不是'null'）
#2
3
The fact that it mentions "the varchar value 'NULL'" indicates that you're trying to set the column's value to the string "NULL" instead of the value NULL.
它提到“varchar值'NULL'”这一事实表明您正在尝试将列的值设置为字符串“NULL”而不是值NULL。
Look in your statement for the word NULL surrounded by quotes. You'll need to remove these quotes.
在你的语句中查找用引号括起来的单词NULL。您需要删除这些引号。
#3
2
Start commenting stuff out one at a time until it stops bombing. Or, take a look and see which value you're passing is NULL for an int column.
开始一次评论一个东西，直到它停止轰炸。或者，看一看并查看int列传递的值为NULL。
#4
2
Do you need to fix the data or can you just convert nulls to 0's for that insert?
您是否需要修复数据，或者只是将空值转换为该插入的0？
If you can just convert, you could wrap your varchars that are getting converted with an ISNULL function. Just put the following around any values that are inserting into int fields.
如果你可以只转换，你可以包装你的varchars转换为ISNULL函数。只需在插入int字段的任何值周围添加以下内容即可。
ISNULL(col1,0)
#1
12
If the value is truly NULL, there wouldn't be a conversion error. However, you have a string = "NULL" then you would get this error.
如果该值确实为NULL，则不会出现转换错误。但是，你有一个string =“NULL”然后你会得到这个错误。
What you could do is...
你能做的是......
NullIf(YourValueHere, 'NULL')
NullIf returns the value of the first parameter if it is not the same as the second parameter. If the parameters are the same, NullIf will return NULL.
如果第一个参数与第二个参数不同，则返回NullIf的值。如果参数相同，NullIf将返回NULL。
Ex:
例如：
Select NullIf('Any Value', 'NULL')
Select NullIf('null','null')
The first will return 'Any Value' and the second will return NULL (not 'null')
第一个将返回'Any Value'，第二个将返回NULL（不是'null'）
#2
3
The fact that it mentions "the varchar value 'NULL'" indicates that you're trying to set the column's value to the string "NULL" instead of the value NULL.
它提到“varchar值'NULL'”这一事实表明您正在尝试将列的值设置为字符串“NULL”而不是值NULL。
Look in your statement for the word NULL surrounded by quotes. You'll need to remove these quotes.
在你的语句中查找用引号括起来的单词NULL。您需要删除这些引号。
#3
2
Start commenting stuff out one at a time until it stops bombing. Or, take a look and see which value you're passing is NULL for an int column.
开始一次评论一个东西，直到它停止轰炸。或者，看一看并查看int列传递的值为NULL。
#4
2
Do you need to fix the data or can you just convert nulls to 0's for that insert?
您是否需要修复数据，或者只是将空值转换为该插入的0？
If you can just convert, you could wrap your varchars that are getting converted with an ISNULL function. Just put the following around any values that are inserting into int fields.
如果你可以只转换，你可以包装你的varchars转换为ISNULL函数。只需在插入int字段的任何值周围添加以下内容即可。
ISNULL(col1,0)