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

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.

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.

If you are on SQL Server 2016 (SP2, CU6 or newer), one option is to turn on trace flag 460 e.g. (QUERYTRACEON 460). The output will indicate the column and offending data.

See this article for details. https://www.brentozar.com/archive/2019/03/how-to-fix-the-error-string-or-binary-data-would-be-truncated/

If you don't care about the truncation you can use SET ANSI_WARNINGS OFFto ignore that type of truncation.