Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

identifying values which would be truncated in an insert query

I was trying to insert some data into a table and I got the error message

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.

Now I know that this was due to trying to insert data which was too big for the column (in this case a VARCHAR(50) ), but it got me thinking about whether or not it was possible to:

a) see what specific values would be truncated (bearing in mind that this could happen for multiple columns), perhaps by giving an output of all of the rows which would be truncated?

b) if within a batch insert there is any way of seeing for each column which would be truncated the maximum input data value (so I could see if the data storage type actually needs to be changed or if it's only one or two cases which would not fit into the column?)

like image 977
Dibstar Avatar asked Feb 28 '11 14:02

Dibstar


People also ask

What is truncated value in SQL?

Definition and Usage The TRUNCATE() function truncates a number to the specified number of decimal places.

How do you know if a String or binary data would be truncated?

One of the most common SQL Server errors, the message “String or binary data would be truncated” occurs when a value is trying to be inserted or updated in a table and it is larger than the maximum field size.

How do I fix String or binary data would be truncated in SQL?

Solution. To avoid this error and to insert the string with truncation, use the ANSI_WARNINGS option. On setting ANSI_WARNINGS to OFF, the error message will not be displayed and the data will be automatically truncated to the length of the destination column and inserted.

How do you fix String or binary data would be truncated The statement has been terminated?

To fix this error, patch to SQL Server 2016 SP2, CU6 or newer (including SQL Server 2017), and then turn on trace flag 460. You can enable it at the query level or at the server level.


1 Answers

There is no particularly satisfactory way of finding the problematic rows.

This blog post has one solution. (SET ANSI_WARNINGS OFF and insert into a staging table then use EXCEPT against the source to find rows that did not import successfully)

like image 104
Martin Smith Avatar answered Sep 30 '22 19:09

Martin Smith