SQL Server 'String or binary data would be truncated' fix

Yeah, that error is a real headache. You're running an INSERT or UPDATE, and SQL Server throws up a generic message that tells you something is too long — but not which column. Let's fix that right now.

The fast fix: find the exact column

In SQL Server 2019 and later (including 2022), you can use a built-in feature called TRUNCATION_WARNINGS if you're in a specific compatibility level. But the real fix that works across all versions is a manual check using LEN() or DATALENGTH() against your source data.

Here's the step-by-step:

1. Identify the table you're inserting into. Let's call it Orders.
2. Look up the column definitions: sp_help 'Orders' or right-click the table in SSMS and select Design.
3. For each varchar, nvarchar, char, or nchar column, check if your source data exceeds the defined length. Use a query like this:

SELECT 
    MAX(LEN(YourSourceColumn)) AS MaxLength,
    'DesiredTable' AS TableName,
    'YourTargetColumn' AS ColumnName,
    50 AS DefinedLength  -- replace with actual column length
FROM YourSourceTable

But that's tedious for many columns. Instead, use this dynamic SQL script that checks all string columns at once:

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 
'SELECT ''' + c.name + ''' AS ColumnName, ' +
'MAX(LEN(' + QUOTENAME(c.name) + ')) AS MaxDataLength, ' +
'MAX(' + CAST(c.max_length AS VARCHAR(10)) + ') AS ColumnMaxLength ' +
'FROM ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + ' ' +
'WHERE ' + QUOTENAME(c.name) + ' IS NOT NULL ' +
'UNION ALL '
FROM sys.columns c
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'Orders'  -- change to your table name
AND ty.name IN ('varchar', 'nvarchar', 'char', 'nchar');

SET @sql = LEFT(@sql, LEN(@sql) - 10);  -- remove last UNION ALL
EXEC sp_executesql @sql;

Run that. You'll see columns where MaxDataLength exceeds ColumnMaxLength. That column is your problem.

Once you know the column, you have two options:

• Increase the column width: ALTER TABLE Orders ALTER COLUMN ProductName VARCHAR(200);
• Truncate the data: Use LEFT(SourceColumn, 50) in your INSERT or UPDATE.

After making the change, your query should work. You'll see the rows inserted without the error.

Why this happens

The error code 8152 fires when you try to put a string into a column that's too short. SQL Server won't guess which column — it just aborts the whole operation. Back in SQL Server 2000, it used to truncate silently. That was bad because you'd lose data. Microsoft added this error to protect your data integrity.

But the error message doesn't tell you the column name because the engine raises the error at the end of the row insert, after checking all columns. It's a design choice that frustrates everyone.

Less common variations

1. SSIS packages

If you're using SQL Server Integration Services (SSIS), the error might show as "Truncation may occur" in Data Flow tasks. SSIS checks lengths at design time. Go to the Advanced Editor of your destination, select Input and Output Properties, find the column, and set Length to match your source. Or use a Data Conversion transform to explicitly trim data.

2. Linked server queries

When querying a linked server, the error can appear even if your local table looks fine. The remote table might have different column lengths. Run SELECT * FROM OPENQUERY(LinkedServer, 'SELECT * FROM RemoteTable WHERE 1=0') to get the column metadata. Compare lengths and adjust your query or remote table.

3. XML data types

You can get this error with XML columns if you're converting a large XML string to the XML type. The XML type has a 2GB limit, but if your string is longer than 8000 characters when casting from VARCHAR(MAX), you might hit it. Use CAST(YourXML AS XML) and check the result.

4. Temporary tables in stored procedures

Sometimes a temp table inherits a column length from a SELECT INTO. If that length is too small for later inserts, you get error 8152. Check the temp table definition with sp_help #TempTable and ALTER TABLE if needed.

Prevention tips

• Set generous column lengths upfront. If a field might hold 200 characters, don't set it to 50. Use VARCHAR(500) unless you have a hard constraint.
• Add CHECK constraints to enforce max lengths at the database level. That way, the application errors out early with a clear message.
• Use stored procedures with parameters that match the destination columns. Test with the longest sample data you have.
• Enable SELECT INTO caution: Before running a SELECT INTO, explicitly define your temp table with CREATE TABLE and set lengths. Avoid relying on implicit definitions.
• Monitor for this error in error logs using SQL Server Agent alerts. Set up an alert for error severity 16, state 1, message 8152. It catches issues before they become data corruption.

That's it. No more guessing which column is too short. Run the dynamic SQL, fix the column, and move on.