SQL Server String or binary data would be truncated

Question

You will need to post the table definitions for the source and destination tables for us to figure out where the issue is but the bottom line is that one of your columns in the source table is bigger than your destination columns. It could be that you are changing formats in a way you were not aware of. The database model you are moving from is important in figuring that out as well.

As others have already said, one of your columns datatypes in the source table is larger than your destination columns.

A simple solution is to simply turn off the warning and allow truncation to take place. So, if you're receiving this error but you are sure it is acceptable for data in your old database/table to be truncated (cut to size) you can simply do the following;

SET ANSI_WARNINGS OFF;
-- Your insert TSQL here.
SET ANSI_WARNINGS ON;

As above, always remember to turn warnings back on again afterwards. I hope this helps.

The issue is quite simple: one or more of the columns in the source query contains data that exceeds the length of its destination column. A simple solution would be to take your source query and execute Max(Len( source col )) on each column. I.e.,

Select Max(Len(TextCol1))
    , Max(Len(TextCol2))
    , Max(Len(TextCol3))
    , ...
From ...

Then compare those lengths to the data type lengths in your destination table. At least one, exceeds its destination column length.

If you are absolutely positive that this should not be the case and do not care if it is not the case, then another solution is to forcibly cast the source query columns to their destination length (which will truncate any data that is too long):

Select Cast(TextCol1 As varchar(...))
    , Cast(TextCol2 As varchar(...))
    , Cast(TextCol3 As varchar(...))
    , ...
From ...

SQL Server 2019 will finally return more meaningful error message.

Binary or string data would be truncated => error message enhancments

if you have that error (in production), it's not obvious to see which column or row this error comes from, and how to locate it exactly.

To enable new behavior you need to use DBCC TRACEON(460). New error text from sys.messages:

SELECT * FROM sys.messages WHERE message_id = 2628

2628 – String or binary data would be truncated in table ‘%.*ls’, column ‘%.*ls’. Truncated value: ‘%.*ls’.

String or Binary data would be truncated: replacing the infamous error 8152

This new message is also backported to SQL Server 2017 CU12 (and in an upcoming SQL Server 2016 SP2 CU), but not by default. You need to enable trace flag 460 to replace message ID 8152 with 2628, either at the session or server level.

Note that for now, even in SQL Server 2019 CTP 2.0 the same trace flag 460 needs to be enabled. In a future SQL Server 2019 release, message 2628 will replace message 8152 by default.

---

SQL Server 2017 CU12 also supports this feature.

Improvement: Optional replacement for "String or binary data would be truncated" message with extended information in SQL Server 2017

This SQL Server 2017 update introduces an optional message that contains the following additional context information.

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'.

The new message ID is 2628. This message replaces message 8152 in any error output if trace flag 460 is enabled.

db<>fiddle demo

---

ALTER DATABASE SCOPED CONFIGURATION

VERBOSE_TRUNCATION_WARNINGS = { ON | OFF }

APPLIES TO: SQL Server (Starting with SQL Server 2019 (15.x)) and Azure SQL Database

Allows you to enable or disable the new String or binary data would be truncated error message. SQL Server 2019 (15.x) introduces a new, more specific error message (2628) for this scenario:

String or binary data would be truncated in table '%.*ls', column'%.*ls'. Truncated value: '%.*ls'.

When set to ON under database compatibility level 150, truncation errors raise the new error message 2628 to provide more context and simplify the troubleshooting process.

When set to OFF under database compatibility level 150, truncation errors raise the previous error message 8152.

For database compatibility level 140 or lower, error message 2628 remains an opt-in error message that requires trace flag 460 to be enabled, and this database scoped configuration has no effect.

One other potential reason for this is if you have a default value setup for a column that exceeds the length of the column. It appears someone fat fingered a column that had a length of 5 but the default value exceeded the length of 5. This drove me nuts as I was trying to understand why it wasn't working on any insert, even if all i was inserting was a single column with an integer of 1. Because the default value on the table schema had that violating default value it messed it all up - which I guess brings us to the lesson learned - avoid having tables with default value's in the schema. :)