SQL Server String or binary data would be truncated

I am involved in a data migration project. I am getting the following error when I try to insert data from one table into another table (SQL Server 2005):

> Msg 8152, Level 16, State 13, Line 1
> String or binary data would be truncated.

The source data columns match the data type and are within the length definitions of the destination table columns so I am at a loss as to what could be causing this error.

The issue is quite simple: one or more of the columns in the source query contains data that exceeds the length of its destination column. A simple solution would be to take your source query and execute `Max(Len( source col ))` on each column. I.e.,

Select Max(Len(TextCol1))
, Max(Len(TextCol2))
, Max(Len(TextCol3))
, ...
From ...

Then compare those lengths to the data type lengths in your destination table. At least one, exceeds its destination column length.

If you are absolutely positive that this should not be the case *and do not care if it is not the case*, then another solution is to forcibly cast the source query columns to their destination length (which will truncate any data that is too long):

Select Cast(TextCol1 As varchar(...))
, Cast(TextCol2 As varchar(...))
, Cast(TextCol3 As varchar(...))
, ...
From ...

You will need to post the table definitions for the source and destination tables for us to figure out where the issue is but **the bottom line is that one of your columns in the source table is bigger than your destination columns**. It could be that you are changing formats in a way you were not aware of. The database model you are moving from is important in figuring that out as well.

this can also happen when you dont have adequate permissions

For the others, also **check your stored procedure**. In my case in my stored procedure `CustomSearch` I accidentally declared not enough length for my column, so when I entered a big data I received that error even though I have a big length on my database. I just changed the length of my column in my custom search the error goes away. This is just for the reminder. Thanks.

I came across this problem today, and in my search for an answer to this minimal informative error message i also found this link:

[To see links please register here]

So it seems microsoft has no plans to expand on error message anytime soon.

So i turned to other means.

I copied the errors to excel:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 8152, Level 16, State 14, Line 13
String or binary data would be truncated.
The statement has been terminated.

(1 row(s) affected)

counted the number of rows in excel, got to close to the records counter that caused the problem... adjusted my export code to print out the SQL close to it... then ran the 5 - 10 sql inserts around the problem sql and managed to pinpoint the problem one, see the string that was too long, increase size of that column and then big import file ran no problem.

Bit of a hack and a workaround, but when you left with very little choice you do what you can.

One other potential reason for this is if you have a default value setup for a column that exceeds the length of the column. It appears someone fat fingered a column that had a length of 5 but the default value exceeded the length of 5. This drove me nuts as I was trying to understand why it wasn't working on any insert, even if all i was inserting was a single column with an integer of 1. Because the default value on the table schema had that violating default value it messed it all up - which I guess brings us to the lesson learned - avoid having tables with default value's in the schema. :)

This can be a challenging error. Here are some notes taken from

[To see links please register here]

look for AmirCharania's comment.

I've adjusted the answer given by AmirCharania for data selected into an actual table, instead of a temp one. First select your dataset into a development table then run the following:


WITH CTE_Dev
AS (
SELECT C.column_id
,ColumnName = C.NAME
,C.max_length
,C.user_type_id
,C.precision
,C.scale
,DataTypeName = T.NAME
FROM sys.columns C
INNER JOIN sys.types T ON T.user_type_id = C.user_type_id
WHERE OBJECT_ID = OBJECT_ID('YOUR TARGET TABLE NAME HERE, WITH SCHEMA')
)
,CTE_Temp
AS (
SELECT C.column_id
,ColumnName = C.NAME
,C.max_length
,C.user_type_id
,C.precision
,C.scale
,DataTypeName = T.NAME
FROM sys.columns C
INNER JOIN sys.types T ON T.user_type_id = C.user_type_id
WHERE OBJECT_ID = OBJECT_ID('YOUR TEMP TABLE NAME HERE, WITH SCHEMA')
)
SELECT *
FROM CTE_Dev D
FULL OUTER JOIN CTE_Temp T ON D.ColumnName = T.ColumnName
WHERE ISNULL(D.max_length, 0) < ISNULL(T.max_length, 999)

Yes,I am also face these kind of problem.

REMARKS VARCHAR(500)
to
REMARKS VARCHAR(1000)

Here, I've change REMARKS filed length from 500 to 1000

I was using empty string '' on on table creation and then receiving error 'Msg 8152, String or binary data would be truncated' on subsequent update. This was happening due to the update value containing 6 characters and being larger than the column definition anticipated. I used "SPACE" to get around this only because I knew I would be updating in bulk following the initial data creation i.e. the column was not going to remain empty for long.

SO BIG CAVEAT HERE: This is not a particularly slick solution but is useful in the case where you are pulling together a data set e.g. for one-off intelligence requests where you are creating a table for data mining, applying some bulk processing/interpretation and storing before and after results for later comparison/mining. This is a frequent occurrence in my line of work.

You can initially populate using the SPACE keyword i.e.

select
Table1.[column1]
,Table1.[column2]
,SPACE(10) as column_name
into table_you_are_creating
from Table1
where ...

Subsequent updates to "column_name" of 10 characters or less (substitute as applicable) will then be allowed without causing truncate error. Again, I would only use this in scenarios similar to that described in my caveat.