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.
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]
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
Let's try to insert some records:
insert into Products(id,code,description)
values (1,'00000-1234','Product A')
We can see that the above insert statement was executed successfully:
select * from Products
Result:
id | code | description
---------------------------
1 |00000-1234 | Product A
Now let's try to insert another record:
insert into Products(id,code,description)
values (2,'00000-34567','Product B')
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.
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')
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')
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
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! :)
--
My latest projects:
DBA Security Advisor: Secure your SQL Server instances against security risks.
In-Memory OLTP Simulator: Easily benchmark SQL Server's In-Memory OLTP Engine against your custom workload.
Artemiou SQL Books: Download my latest free books on SQL Server.
Artemiou Data Tools: See my latest software project.