When inserting or updating data in a table, it is possible to encounter an error message that reads “String or binary data would be truncated”. This occurs when the value being inserted or updated is larger than the maximum field size. To avoid this error and insert the string with truncation, the ANSI_WARNINGS option should be set to OFF. This will prevent the error message from being displayed and the data will be automatically truncated to the length of the target column and inserted.
If you want to omit string truncation at a certain time or operation, you can do so in SQL Server by using the SET ANSI_WARNINGS OFF command. This can be useful when creating a table and then attempting to update it with an empty string. When set to ON at database compatibility level 150, truncation errors generate the new error message 2628 to provide more context and simplify the troubleshooting process. To identify and solve text truncation issues when using the INSERT SELECT statement, a useful store procedure can be written.
