How to fix String or binary data would be truncated

29 May,2020 by Jack Vamvas

Question: I'm trying to run an INSERT statement , on a table with a large amount of columns and millions of rows. When executing the query statement , I'm getting an error message:

INSERT INTO myTable
SELECT * FROM myTable1 ;

Msg 8152, Level 16, State 30, Line 19
String or binary data would be truncated.
The statement has been terminated.

 

 How can I fix this issue? Trying to find which specific row and column is causing the problem is the immediate problem. Once I can find which data is triggering the error , I can make the adjustment 

Answer: This is one of the most common errors with INSERT statements. Often trying to find the source of the issue is tricky. Especially if you do not have access to the the column definitions from the source\ target tables. 

SQL Server 2017  (CU12 and above) introduced the trace flag 460. 

Taking the code you supplied , the trace flag 460 can be enabled as:

 

DBCC TRACEON(460, -1)

GO

INSERT INTO myTable
SELECT * FROM myTable1 ;

DBCC TRACEOFF(460, -1)

GO


Now when you execute the statement , you'll get something like

Msg 2628, Level 16, State 1, Line 21
String or binary data would be truncated in table 'mydb.dbo.myTable', column 'myColumn1'. Truncated value: 'some_value'.

 

The the information supplied in this error message and make any adjustments required on either the data , column width or data type.

If you are using SQL Server 2019 - there is the VERBOSE_TRUNCATION_WARNINGS  option - enabled through the ALTER DATABASE SCOPED CONFIGURATION

 

Read More on DBCC and trace flags

List SQL Server DBCC commands using trace 2520 – documented and undocumented commands

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to fix String or binary data would be truncated


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer