Ignore or disable string truncation error and insert or update records

String or binary data would be truncated error message is a well known error message and you get this when you try to set a value with higher characters than the column accepts. For an example, if you have configured a column with varchar(5) and you try to set a value with 6 characters, you will get the truncation error and statement will be failed.

Can we ask SQL Server to ignore this and continue with truncation and set the value with possible characters? Yes, it is possible with ANSI_WARNINGS setting. The default value of this is ON, hence INSERT and UPDATE statements work as specified by the ISO standard. That is the reason for the error we experience.

If you set this setting for OFF, then no warning is issued and statement succeeds. However value is truncated as per the column size set.

Here is an example;

USE tempdb;
GO

DROP TABLE dbo.TestTable
GO

CREATE TABLE dbo.TestTable
(
 Id int PRIMARY KEY
 , Value varchar(5) NOT NULL
);
GO

-- This statement fails as we send 11 characters
INSERT INTO dbo.TestTable
 (Id, Value) VALUES (1, 'Test value.');

-- This code succeeds but will take only first 5 characters
SET ANSI_WARNINGS OFF
INSERT INTO dbo.TestTable
 (Id, Value) VALUES (2, 'Test value.');
SET ANSI_WARNINGS ON