String or binary data would be truncated error and solution

Hello friends, today I am going to talk about a very common error message that we receive while inserting data into a table i.e. “string or binary data would be truncated”.

What this error means:- Before moving to the solution, we should now what is causing this error message. We generally get this error message when we try to insert a string data into a column(char, varchar,nvarchar,nchar) having lesser length than the string data. Lets take an example to understand the fact.

I have created a table using below script in SQL Server :-

Create table DummyData(Name varchar(10),Address char(10),City nvarchar(10))

Lets try to insert data into this table using below script :-

insert into DummyData values('Neeraj Kumar Yadav','S.G.M Nagar','faridabad')

We can see in the above screenshot, there is an error message received (encircled by blue color) “string or binary data would be truncated”. Lets check why did we get this error message.

Lets check the length of the data which is being inserted using below query.

select DATALENGTH('Neeraj Kumar Yadav'), DATALENGTH('S.G.M Nagar'), DATALENGTH('faridabad')

We can see in the above screenshot that data length of Name, Address and City is 18, 11 and 9 respectively. While each column length is 10. Hence we are getting the error message, lets try to insert Data with correct length. I have short down the data length of the values which are more than 10.

Lets execute this query, it will work fine.

insert into DummyData values('Neeraj','S.G.MNagar','faridabad')

In the above screenshot we can see that data got inserted successfully.

How to identify which column is causing the problem in bulk insert:- Now , I am going to tell you, how to troubleshoot similar issue while bulk insert.

I have created one more table “RealData” using below query.

create table RealData(Name varchar(9),address varchar(10),City varchar(10))

Lets try to insert data from “DummyData” to “Realdata” using below query and see the results.

We are getting same error message, lets find out which column is creating the problem.

I have run below query to find out the maximum string length from all the columns of "dummydata" table.

select max(len(Name)),max(len(Address)),max(len(City)) from DummyData

As we can see that “Name” column has maximum string length 10 while, in “RealData” table, “Name” column has length 9, it means that is the problem area. Now we can solve this problem by increasing the column(Name) length of “Realdata” table by 1 length.

Alter table RealData alter column Name varchar(10)

insert into RealData(Name,Address,City)

select Name,Address,City from DummyData

We will run above query and this query will run successfully without any loss of data. I hope you will find this article useful for you, please feel free to comment on this article.