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

pmscorca 1,007 Reputation points
2024-10-31T17:10:14.1766667+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,133 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,665 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 26,201 Reputation points
    2024-10-31T17:37:45.1566667+00:00

    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/


  2. LiHongMSFT-4306 29,111 Reputation points
    2024-11-01T02:07:42.5666667+00:00

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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.