One of the more annoying things that I have dealt with in SQL Server is inserting a large amount of data, only to have this message appear.

Msg 8152, Level 16, State 30, Line 14

String or binary data would be truncated.

The statement has been terminated.

When you are importing a large number of rows, this is an annoying message. For me, anything more than 100 rows is large because I can't easily dig through a file and find out which rows are problematic. When I have 1000 or more rows, this makes me want to scream.

Fortunately, SQL Server 2019 helps me deal with this by providing more information. It's not perfect, but I'll show you why a recent upgrade to 2019 was helpful. I also note that you can get this on SQL Server 2016 and 2017.

A Simple Situation

Let me demonstrate what I've seen by looking at an example in SQL Server 2016 and 2019. First, 2016.

I build a small table:

CREATE TABLE StringInsertTest
( myid INT NOT NULL,
  mystring VARCHAR(10)
)

Simple and easy. Now, let's insert a small string. This works easily on 2016, as we see:

If I add a longer string, this fails on 2016, with no indication of where this failed. With a single insert, I can figure this out, but the error message isn't very helpful.

Now let's create the same table on SQL Server 2019. If I run the insert for a short string, obviously that works the same. Now let's look at the longer string. It still fails, but now I can see this failed on the value that starts with "this is mo".

If I insert two rows on 2019, there is also a failure, but I can see again that the string that starts with "the quick " is the one that failed.

Better Error Messages

If I search for this error message and SQL Server 2019, I find a Knowledge Base article that specifies this is better. The article is titled "Improvement: Optional replacement for "String or binary data would be truncated" message with extended information in SQL Server 2016 and 2017" and explains there actually is a new error message and that there is more information included. The actual change in T-SQL syntax is:

Msg 2628, Level 16, State 6, Procedure ProcedureName, Line Linenumber
String or binary data would be truncated in table '%.*ls', column '%.*ls'.
Truncated value: '%.*ls'.

This error means that the %.ls is replaced with the table name, the column name, and the value in question. This can help you with troubleshooting. The article says the line number was in the old message, but that wasn't something I remember seeing.

There also is a note that you can add a trace flag and get the error message. Let's try that now. I'll run this code:

DBCC TRACEON(460)

Which completes. Now I'll run my code. Sure enough, I get the new error message. I don't know how I missed this, but if you are on SQL Server 2016 SP2 CU 6 or SQL Server 2017 CU 12, then you should enable trace flag 460.

Larger Loads

For me, insert statements aren't usually a problem. The bigger issue is when I bulk load a lot of data. As an example, I'll show a data load of Oscar data from movies. Let's assume there is a database in place, which has a table like this one, to capture the Oscar scores for various movies over the years:

CREATE TABLE dbo.OscarScores
( [year] INT
, [score] INT
, film VARCHAR(30)
)

In this case, I have data in here, and I can't alter the schema. I get a load of data to process. I create a staging table for this data. Usually I would make a generic one, but for the purposes of this demonstration, I actually did this for Robert DeNiro's movies. He's amazing. I set up a table for the large load and insert the data. I do this often, usually using a quick wizard with the "Import Flat File" process or I have an SSIS package for loads I repeat.

CREATE TABLE dbo.DeNiro
( [year] INT
, [score] INT
, film VARCHAR(max)
)

My normal process here is to merge the data in some way, but in many cases I just insert new data as it's all new. I can run this code:

INSERT dbo.OscarScores (year, score, film)
 SELECT dn.year, dn.score, dn.film
  FROM dbo.DeNiro AS dn

In the past, I would get the "string or binary data truncated" and I'd have to search through a CSV, find the bad data, and either change it and ask how to handle it. Often, we manually truncate data somehow, but finding the data is a problem. With the new error message, this is easy. In my case, I see this:

Msg 2628, Level 16, State 1, Line 23
String or binary data would be truncated in table 'sandbox.dbo.OscarScores', 
column 'film'. Truncated value: ' "Dear America: Letters Home F'.
The statement has been terminated.

The issue here is the movie, Dear America: Letters Home From Vietnam, is longer than 30 characters. Bad database design, but easy to catch which is the offending row. In my dataset, this is row 21. If I correct this and re-run the insert, I find a second error in row 52 for "The Adventures of Rocky & Bullwinkle". I can correct that data and the import proceeds fine.

Summary

This error is one of the more maddening ones for someone importing data. Not because it's wrong, but because it makes my job harder when trying to solve a problem. I get bad data, the design doesn't support it, and I need to find the bad data. This error message in 2019 really helped.

It's good to know I can also get this in SQL Server 2015 and 2017 with a trace flag if I'm patched. A good argument for patching my servers.