Data truncation error in SQL Server? Here’s how to find the problem using Excel

I was running a query in SQL Server Management Studio that was appending data from one table into another. I received the following error:

String or binary data would be truncated

Update: here’s how to solve the data truncation error with SQL.

The Quick Answer

This happens when you’re trying to insert too many characters into a field. For example, a field may be VARCHAR(1) but you’re trying to put in 2 characters.

Figuring Out The Problem

Is it bad data? Or should the table structure be updated?

First, put the data into Excel:

10 points, of no value, if you know where this dataset is from :)

Use the LEN() function to find the number of characters in each field

💡 Tip: here’s how you can quickly copy an Excel function to other cells

Add on a filter:

Look at each column individually to see the largest number of characters. Compare this to the length of each column in the database table:

In the above example, I can see there’s a string that’s 16 characters long. My database field only allowed for 15.