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;

view plainprint?

1. USE tempdb;  
2. GO  
3.   
4. DROP TABLE dbo.TestTable  
5. GO  
6.   
7. CREATE TABLE dbo.TestTable  
8. (  
9.  Id int PRIMARY KEY  
10.  , Value varchar(5) NOT NULL  
11. );  
12. GO  
13.   
14. -- This statement fails as we send 11 characters  
15. INSERT INTO dbo.TestTable  
16.  (Id, Value) VALUES (1, 'Test value.');  
17.   
18. -- This code succeeds but will take only first 5 characters  
19. SET ANSI_WARNINGS OFF  
20. INSERT INTO dbo.TestTable  
21.  (Id, Value) VALUES (2, 'Test value.');  
22. SET ANSI_WARNINGS ON  

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