SQL INSERT，强制截断字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/436023/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL INSERT, Force Truncate Field
提问by Ben Aston
I have input data containing some "rogue" fields that are longer than the corresponding database field. This causes my import script, which uses SQL INSERT statements to fall over with a warning:
我的输入数据包含一些比相应数据库字段长的“流氓”字段。这会导致我使用 SQL INSERT 语句的导入脚本失败并显示警告：
Msg 8152, Level 16, State 13, Line 2
String or binary data would be truncated.
How can I force truncation of these fields and enable my script to complete?
如何强制截断这些字段并使我的脚本能够完成？
回答by Ian Jacobs
When you insert do something along the lines of :
当您插入时，请执行以下操作：
INSERT INTO Table1('Column1') VALUES(LEFT(RTRIM(InputField), MaxLength))
You'll only store the Left N characters to the DB.
您只会将左 N 个字符存储到数据库中。
回答by Meff
Use Ansi Warnings? http://msdn.microsoft.com/en-GB/library/ms190368(v=sql.110).aspx
使用 Ansi 警告？http://msdn.microsoft.com/en-GB/library/ms190368(v=sql.110).aspx
DECLARE @TABLE TABLE
(
Data VARCHAR(3)
)
SET ANSI_WARNINGS OFF
INSERT INTO @TABLE
VALUES('Hello World')
SELECT * FROM @TABLE
SET ANSI_WARNINGS ON
INSERT INTO @TABLE
VALUES('Goodbye World')
SELECT * FROM @TABLE
回答by HLGEM
Personally I want them to fail and then look at the data. I do not believe you should ever consider automated truncation of data. You could actaully need to make your field defintion larger. You won't know unless you visually inspect the rows that would fail.
我个人希望他们失败，然后查看数据。我不认为您应该考虑自动截断数据。您实际上可能需要使您的字段定义更大。除非您目视检查会失败的行，否则您不会知道。
Example: We store Speech titles. Suppose you had two titles:
How to Read a Book:
How to read a Book in the Bathtub without getting it wet
示例：我们存储语音标题。假设您有两个标题：
如何阅读一本书：
如何在浴缸中阅读一本书而不弄湿
Now if your field had 10 characters both would get truncated to the same thing. Moreover what they got truncated to wouldn't even make sense. Both would say "How to Rea" Now you don't have a difference bewteeen two very differnt titles and what you do have isn't correct anyway. Adjusting the field to store some higher amount of characters is clearly the correct thing to do. This example is silly, but garbage in, garbage out. If you truncate the names of people or other critical data, you will eventually have problems because you have garbage data. I've seen this happen with our speech titles, with last_names, with addresses, with phone numbers and lots of other data. If you have to truncate the data, more than likely you are storing useless data for that record anyway. Either the data needs correct cleanup (Such as removing the ()- from a phone number if you are designed only store the numbers) before importing into the production data or the field needs to be larger.
现在，如果您的字段有 10 个字符，两者都会被截断为相同的内容。此外，他们被截断的内容甚至没有任何意义。两者都会说“How to Rea” 现在您在两个非常不同的标题之间没有区别，无论如何您所拥有的都是不正确的。调整字段以存储更多字符显然是正确的做法。这个例子很愚蠢，但垃圾进，垃圾出。如果您截断人名或其他关键数据，最终会出现问题，因为您有垃圾数据。我已经在我们的演讲标题、姓氏、地址、电话号码和许多其他数据中看到了这种情况。如果您必须截断数据，很可能无论如何您都在为该记录存储无用的数据。
回答by Cade Roux
I don't know of an easier way than tracking them down and using LEFT(). I'd love to know if there is, though.
我不知道比追踪它们并使用LEFT(). 不过，我很想知道是否有。
Obviously, using the metadata like INFORMATION_SCHEMA.COLUMNSyou can determine this automatically and script it to do a LEFT()to the appropriate length.
显然，使用像INFORMATION_SCHEMA.COLUMNS您这样的元数据可以自动确定并编写脚本以执行LEFT()适当的长度。
