December 22, 2005 at 12:47 pm
When we insert/update a string that is bigger than the size of the column we get following error
String or binary data would be truncated.
The statement has been terminated.
My question is "Is there an easy way to know for which column the problem is coming,in case of insert, and for which row value (or string)"
Because sometimes a table might be having lot of cloumns and when we are inserting bulk data from another source it's kind of difficult to know which column the error is coming and for what string value
(probably if we know the column name then finding the string values whose size is bigger than the column should be easy, we can simple run a query based on "len" but I was just trying to show a proper error message like "the problem is with so and so columns and so and so record"...)
December 22, 2005 at 1:20 pm
When I had to do this in the past what I did was create a table with fields I knew would be large enough. Then ran the LEN function against the columns and was able to determine where conflicts could occur.
December 25, 2005 at 11:16 pm
LEN will not work as expected in this situation, if you have trailing spaces in the column that is inspected, LEN will strip these out before calculating the length, you can use DATALENGTH. So a column with 10 characters and 10 trailing spaces (CHAR(20)) would return LEN of 10, but will still error when attempting to insert into a varchar(15) column.
I believe that the best approach is to always RTRIM(LEFT(LTRIM(RTRIM(mycolumn)),{column size})) each field in the INSERT or UPDATE statement, to eliminate the error.
You could then compare the MAX(DATALENGTH()) to find any that were truncated.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply