String or binary data would be truncated, field find

Question

I understand the error and how to fix I am just interested in finding the field to fix. Let me start from the top. I am running a scheduled task daily which executes a process that at some points runs some sprocs in sql which run insert statements. Unfortunately after checking my logs I am getting the error in question and therefore my sprocs arent working. I could update every field to a bigger length and this would probably fix it but id rather not. Is there any way of knowing (without manually checking as there are many fields and thousands of rows) the field that contains the value that is too big for the field it is being inserted into?

Answer 1

Import the data into a new table using VARCHAR(MAX) as the datatype for the columns. Then you can use DATALENGTH to get the maximum size of each column.

SELECT MAX(DATALENGTH(col1)) AS col1, MAX(DATALENGTH(col2)) AS col2, etc.
  FROM newTable

This will tell you which column(s) exceed the size of your column(s).