String or binary data would be truncated

String or binary data would be truncated

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.

 

Reproducing the “String or binary data would be truncated” error

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:

 

Featured Database Productivity Tools

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

Snippets Generator - SQL Snippets Creation Tool

Learn more

 

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

Dynamic SQL Generator: Easily convert static SQL Server T-SQL scripts to dynamic and vice versa.

Learn more

 

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Check out Artemakis’s eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Loading...

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

© SQLNetHub

 

Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Artemakis's official website can be found at aartemiou.com.

One thought on “String or binary data would be truncated

  1. Ameenulla

    i am getting the same problem.i am dumping data from one table to another(insert in to table 1 select * from table2) , here my requirement is insert the valid record and discard the invalid record whose lenth is exceeding .