How to avoid the error “string or binary data would be truncated” and insert string with truncation in SQL Server?

Problem

Part of my job is migrating data to SQL Server from any classic database using insert scripts. During such data migrations, I have frequently encountered the error message “String or binary data would be truncated…” and the insert statement terminates. In most cases this can be avoided by taking several precautions like trimming, translate or replacing unwanted characters from the string. However, in some cases we may not need the extra length of the string and can be trimmed and inserted. In such situations, there is an option in SQL Server to avoid the error and insert the data by automatically trimming it. Let us see how to use the option with an example.

To start with let us see what will happen when we try to insert a longer string in a column of smaller length. In this sample table MTB_Table_A, the column Name is of 10 nvarchar in length. To this column, I am trying to insert a string of length 15.

CREATE TABLE [MTB_Table_A](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](10) NULL
) ON [PRIMARY]
GO

INSERT INTO MTB_Table_A VALUES ('James D Madison')
GO

/* Result */
Msg 2628, Level 16, State 1, Line 1
String or binary data would be truncated in table 'Test.MyTecBits.com.dbo.MTB_Table_A', column 'Name'. Truncated value: 'James D Ma'.
The statement has been terminated.

Solution

To avoid this error and to insert the string with truncation, use the ANSI_WARNINGS option. On setting ANSI_WARNINGS to OFF, the error message will not be displayed and the data will be automatically truncated to the length of the destination column and inserted. Here is the previous insert statement with SET ANSI_WARNINGS OFF.

SET ANSI_WARNINGS OFF
INSERT INTO MTB_Table_A VALUES ('James D Madison');
SET ANSI_WARNINGS ON
GO

This will be helpful when you are sure you are migrating less important data. However, it is not a good idea to use important data like phone numbers, SSN numbers, etc…

Warning

• After inserting the data do not forget to switch ON the ANSI_WARNINGS setting. Keeping the ANSI_WARNINGS OFF may lead to truncation of important string values without your knowledge while running further migration scripts.
• SET ANSI_WARNINGS OFF will stop issuing warning on errors in math operations, arithmetic over flow errors, divide by zero errors, null value in aggregate functions, etc… So use it with care.

Reference

• Read more about SET ANSI_WARNINGS at Microsoft Docs.