SQL Server - String or binary data would be truncated.

Asked By Daniel on 26-Sep-11 09:55 AM
Hi everybody!I am getting the following error when inserting into a table.
INSERT  dbo.FEDERATION (IDFederation,FederationName)
VALUES('FED012','FERWACOOP')

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


Sakshi a replied to Daniel on 26-Sep-11 10:09 AM

check the size of the column in db table.
Guess the size of col in db table should be lesser than the value you are passing.
dipa ahuja replied to Daniel on 26-Sep-11 10:12 AM
The reason of this error " 'String To Binary data would be truncated'" is when you are try to insert the data in a table whose size (length) is less than the data we are inserting into it.

So increase the size of you table field in which you are getting this error
Reena Jain replied to Daniel on 26-Sep-11 10:33 AM
Hi,

One of the fields on table SeekerDetails of type varchar/char is being fed data that is too long.

Errors of the Severity Level 16 are generated by the user and are corrigible by the user. The statement cannot be executed this way. You must either shorten the string to be isnerted to widen the column.
Suchit shah replied to Daniel on 26-Sep-11 10:33 AM
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 http://aartemiou.blogspot.com/search/label/Row%20Constructors) 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! :)
Venkat K replied to Daniel on 26-Sep-11 10:41 AM
It's a very common error, the string which you are trying to insert is greater then the length of the column you have declared.

check the column lengths against the strings which you are inserting.

Thanks
Anoop S replied to Daniel on 26-Sep-11 03:19 PM
occurs when we try to insert or update data which is passed having length greater than which is specified in the database.

For Example

    Column Name    Data Type

    Description        varchar(50)

    But we given more than 50 characters its will be truncated.