How detecting the field that causes "string or binary data would be truncated" error

Question

Hi,

I'm working on a SQL Server 2017 (RTM-CU31-GDR) instance and I'm testing an INSERT INTO ... SELECT ... FROM ... statement that unfortunately causes a "string or binary data would be truncated" error.

If possible, I'd like to save development time trying to detect which field/value causes this error in a simple and automatic manner without checking each field.

So, I've tried to use the clause OPTION (QUERYTRACEON 460) but it doesn't return any helpful additional message to detect the wrong field/value.

Any suggests to me, please? Thanks

Answer

Hi @pmscorca,

It is an old SQL Server behavior that was fixed in latest version(s) without any trickery.

Check it out few solutions here: https://www.brentozar.com/archive/2019/03/how-to-fix-the-error-string-or-binary-data-would-be-truncated/

Answer

Hi @pmscorca

Try this query sample which compares the lengths of the source data to the maximum lengths defined in the target table.

USE tempdb;
GO
CREATE TABLE TargetTable(COL1 char(1));
CREATE TABLE SourceTable(COL2 char(3));
INSERT INTO SourceTable VALUES('AAA'),('BB'),('C');
GO
INSERT INTO TargetTable(COL1) SELECT COL2 FROM SourceTable 
--
SELECT *,CASE WHEN LEN(COL2) > (SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TargetTable' AND COLUMN_NAME = 'COL1') THEN 'YES'
              -- Add additional checks for other columns if necessary
              ELSE 'NO' END AS TruncationCheck
FROM SourceTable;

Best regards,

Cosmog

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Share via

How detecting the field that causes "string or binary data would be truncated" error

2 answers

Your answer