String or binary data would be truncated

There are many times where a query may result to the following error: String or binary data would be truncated. The statement has been terminated.

We will go through a simple example for explaining this behavior.

Creating the Sample Table

Consider the following table:

CREATE TABLE [dbo].[Products](
[id] [int] NOT NULL,
[code] [varchar](10) NULL,
[description] [varchar](100) NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
[id] ASC
)) ON [PRIMARY];
GO

 

In our scenario, we will use the Products table which as the name implies, it stores information about products.

The table has three columns:

• id of type int which is also the primary key
• code of type varchar with size 10
• description of type varchar with size 100

 

Populate the Table with Sample Data

Let’s try to insert some records:

insert into Products(id,code,description)
values (1,'00000-1234','Product A');
GO

We can see that the above insert statement was executed successfully:

select * from Products;
GO

Result:

id | code | description
—————————
1 |00000-1234 | Product A

 

Getting the Error Message

Now let’s try to insert another record:

insert into Products(id,code,description)
values (2,'00000-34567','Product B');
GO

In this case, the above insert statement returns an error:

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

 

Why we got the String Truncation Error Message

The above error occurs because the size of the code value (11) in the insert statement exceeds the allowed size (10).

If we take a look at the table’s definition, we can see that the size of the code column is 10.
Though the size of the value (00000-34567) in the insert statement is 11 characters. In such cases SQL Server returns the abovementioned error.

If we also try to insert multiple records (i.e. by using row constructors) and there is even one record which contains a value that exceeds the allowed size defined for the specific field in the table, then the whole statement fails:

insert into Products(id,code,description)
values (1,'00000-1234','Product A'),(2,'00000-34567','Product B'),(3,'00000-3456','Product C');
GO

Error Message:

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

Now let’s try something else:

insert into Products(id,code,description)
values (2,cast('00000-34567' as varchar(10)),'Product B');
GO

Within the above statement the 11-characters code value was converted to varchar(10) and the insert statement was executed successfully.

Though if we see the records in the Products table we can see that the last character (7) was not included.

select * from Products;
GO

Result:

id | code | description
—————————
1 | 00000-1234 | Product A
2 | 00000-3456 | Product B

As we can see, there are two approaches handling this issue: either filter your data correctly (with respect to the size of characters for the columns in the table’s definition) before trying to insert them into the table, or use a SQL Server function (i.e. cast, convert, substring, etc.) in order to automatically remove the redundant characters. The choice is yours!

Of course, you can always use larger column sizes I guess! 🙂

Read Also

Feel free to check our other relevant articles on SQL Server troubleshooting:

• Error converting data type varchar to numeric
• Error converting data type varchar to float
• SQLServerAgent could not be started (reason: Unable to connect to server ‘(local)’; SQLServerAgent cannot start)
• ORDER BY items must appear in the select list if SELECT DISTINCT is specified
• There is no SQL Server Failover Cluster Available to Join
• There is insufficient system memory in resource pool ‘internal’ to run this query.
• The SELECT permission was denied on the object ‘extended_properties’, database ‘mssqlsystemresource’, schema ‘sys’.
• … all SQL Server troubleshooting articles

 

Featured Database Productivity Tools

Snippets Generator: Create and modify T-SQL snippets for use in SQL Management Studio, fast, easy and efficiently.

Learn more

 

Dynamic SQL Generator: Convert static T-SQL code to dynamic and vice versa, easily and fast.

Learn more

 

Subscribe to our newsletterand stay up to date!

Check out our latest software releases!

Check out Artemakis’s eBooks!

 

Rate this article: (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub