Source value that generates generic t-sql error 'String or binary data would be truncated'?

Question

I'm inserting data from one table with twenty columns & 300 rows to another table. Mid-run, I get error

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

I know what the error means, but how do I find which value, or at least which row, is generating this error?

The INSERT statement is something straightforward like this:

Insert into Employee (col1, col2, col3,..., col20) 
select col1, col2, col3,..., col20 from TEMP_EMPL

Thanks.

There's no way to tell. Feel free to vote for this feature! To narrow it down, you could check max(len(col1)) for each column to find the bad column, and then find rows where len(col1) > [max allowable] to find the row(s). — Blorgbeard
It's going to be a string that's too long, most likely. Do you have a way to "measure" the string length of all values (as a separate operation from trying the insert)? If you're waiting till you actually attempt the INSERT, all you're ever going to get is that generic error. — SlimsGhost
This is the dumbest issue from MS ever. They obviously know at run time which column causes this...there has been a connect item to fix this for years but they continue to ignore it. You have to manually look at your data to figure this out. UGH!!!! — Sean Lange
One thing you can do is to use select into as an analysis tool instead of your insert. Then you can compare columns to the Employee table to determine which column(s) are longer in your data. It is almost always due to a data size mismatch. — Sean Lange
@SeanLange -- They ignore connect issues for every piece of software they make, every time. It's maddening. — rory.ap

Damian Damian · Accepted Answer · 2016-10-18T20:39:55

According to this answer it's not directly possible. There is also an old support ticket to add the name of the column to this error message.

To find the reason you can disable this error by setting

set ANSI_WARNINGS  OFF

insert the data and compare the maximum length of the column with current maximum length of the data. After that you can limit your search to columns that are filled to the maximum. For example you can use the following code (just change @TableName and @TableSchema):

DECLARE @TableName sysname = 'test', @TableSchema sysname = 'dbo'
DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((SELECT 
'
UNION ALL 
select ' + QUOTENAME(Table_Name,'''') + ' AS Table_Name, ' + 
QUOTENAME(Column_Name,'''') + ' AS ColumnName, MAX(' + 
CASE WHEN DATA_TYPE IN ('XML','HierarchyID','Geometry','Geography','text','ntext')
THEN 'DATALENGTH(' ELSE 'LEN(' END + QUOTENAME(Column_Name) + 
')) as [Max Length], ' + QUOTENAME(C.DATA_TYPE,'''') + ' AS Data_Type, ' + 
CAST(COALESCE(C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_SCALE,0) AS VARCHAR(10)) + 
'  AS Data_Width FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(Table_Name)
FROM INFORMATION_SCHEMA.COLUMNS C 
WHERE TABLE_NAME = @TableName 
AND table_schema = @TableSchema
--AND DATA_TYPE NOT IN ('XML','HierarchyID','Geometry','Geography')
ORDER BY COLUMN_NAME 
FOR XML PATH(''),Type).value('.','varchar(max)'),1,11,'')  
--print @SQL

EXECUTE (@SQL)

Source value that generates generic t-sql error 'String or binary data would be truncated'?

1 Answers