Rajeev Pentyala – Microsoft Power Platform

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.

Reason:

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

Fix:

• 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 @SQLStatement VARCHAR(MAX);
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 ‘
FROM
SYS.COLUMNS cols
JOIN
SYS.TYPES types
ON
types.system_type_id = cols.system_type_id and types.name != ‘sysname’
WHERE
cols.OBJECT_ID = OBJECT_ID(@TableName);

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

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

• 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

🙂