SQL Server Insert Into - How to identify the column that is causing truncation error

I have a stored procedure that inserts 650 fields into a table. The insert is failing with a truncation error.

It's a simple

INSERT INTO
SELECT (a bunch of fields) 
FROM (a bunch of tables)

Below is the error message:


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

Is there a quick way that I can identify what field is causing the truncation error?

The fact that the select statement to be inserted into the table has 650 fields makes it difficult to pinpoint which field is causing the truncation error.

I am thinking I can maybe comment out blocks of fields at a time so as to only have the SP insert 100 fields at a time and then run the SP 6 or 7 different times until i can at least narrow down to a group of 100 fields that will contain the field that is causing the truncation error.

Alternatively I am thinking that maybe I can just SELECT INTO a new table and then compare the data lengths in the table vs the data lengths of the target table I am trying to insert into in my SP to see which field contains a longer than expected field length...

I am using SQL Server 2014.

Any easier alternatives?

Unfortunately, you have encountered quite old a "feature". There's been a Connect ticket open since 2008, and for almost ten years this hasn't been significant enough to warrant a fix.

Update: For those interested about the history of the issue, the Connect site has long since been retired. The original suggestion can still be viewed via Wayback Machine. As per another answer, use the trace flag to see the colun name.

The standard workaround is, like you figured, a select into... followed by comparing table metadata. Another possibility is binary searching the offending column, but that's manual work too. There are some hacks for metadata comparison, but simple, elegant solution doesn't exist. Maybe some third-party tools would be of help, but I am not aware of such.

StackExchange Database Administrators Q#179468, answer score: 10