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

Have you tried converting to a varchar of a certain length? If it is greater than the specified length it would cut off the end.

Answer 2

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.

Answer 3

I don't know of an easier way than tracking them down and using LEFT(). I'd love to know if there is, though.

Obviously, using the metadata like INFORMATION_SCHEMA.COLUMNS you can determine this automatically and script it to do a LEFT() to the appropriate length.

Answer 4

Which Database? Some can be configured to silently truncate instead of erroring out. Then you won't have to modify all the relevant import statements.