SQL INSERT, Force Truncate Field

Question

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:

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?

Answer 1

Use Ansi Warnings? 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

Answer 2

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.

Answer 3

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.