Home > SQL > SQL Server- Troubleshoot ‘String or binary data would be truncated’ error

SQL Server- Troubleshoot ‘String or binary data would be truncated’ error

In one of my Data Migration requirement, I had to join data from multiple tables and  move to a single table. Requirement is complex as data is around 20 GB and table has around 300 columns.

So, I built a Stored Procedure to join tables and move data to single table, the query ran for an hour and finally failed with below error:

String or binary data would be truncated.


  • Length of the data I was trying to insert to is more than the defined column size in target table.


  • Identifying the problematic column was difficult as my source tables had 300 columns.
  • To trouble the issue, I ran a query on my source tables to get max data length of all columns, which helped me to identify the mismatched column.
  • Below query helped me to identify the maximum data length of source table columns

DECLARE @TableName sysname;
–Set your table name here
SET @TableName = ‘Product‘;
SET @SQLStatement = ”;

SELECT @SQLStatement = @SQLStatement + ‘SELECT ‘ + QUOTENAME(cols.name, ””) + ‘ AS ”Column Name”, ‘ + QUOTENAME(types.name, ””) + ‘ AS ”Data Type”, ‘ +
QUOTENAME(cols.max_length, ””) + ‘ AS ”Defined Length”, MAX(DATALENGTH(‘ + QUOTENAME(cols.name) + ‘)) AS ”Max Data Length” FROM ‘+@TableName+ char(10) +’ UNION ‘
types.system_type_id = cols.system_type_id and types.name != ‘sysname’
cols.OBJECT_ID = OBJECT_ID(@TableName);

SET @SQLStatement = LEFT(@SQLStatement, LEN(@SQLStatement)-6)
— Execute SQL statement

  • To simplify the understanding I created a ‘Product’ table with 4 columns and added few rows

Product Table_1

  • Execute the query and it lists out ‘Column Name’,’Data Type’, ‘Defined Column Length’ and ‘Max Data Length’.
    • In the below result, my ‘Description’ column’s max data length is 142

Product Table_2



  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: