String or binary data would be truncated

Question: I'm trying to run an INSERT statement , on a table with a large amount of columns and millions of rows. When executing the query statement , I'm getting an error message:

INSERT INTO myTable
SELECT * FROM myTable1 ;

Msg 8152, Level 16, State 30, Line 19
String or binary data would be truncated.
The statement has been terminated.

 How can I fix this issue? Trying to find which specific row and column is causing the problem is the immediate problem. Once I can find which data is triggering the error , I can make the adjustment 

Answer: This is one of the most common errors with INSERT statements. Often trying to find the source of the issue is tricky. Especially if you do not have access to the the column definitions from the source\ target tables. 

SQL Server 2017  (CU12 and above) introduced the trace flag 460. 

Taking the code you supplied . The trace flag 460 can be enabled for a session  or as a permanent at startup 

Method 1 - enable a trace for a sessions

DBCC TRACEON(460, -1)

GO

INSERT INTO myTable
SELECT * FROM myTable1 ;

DBCC TRACEOFF(460, -1)

GO

Method 2 - Set up as a SQL Server startup parameter

How to enable SQL Server trace flags at startup

Now when you execute the statement , you'll get something like

Msg 2628, Level 16, State 1, Line 21
String or binary data would be truncated in table 'mydb.dbo.myTable', column 'myColumn1'. Truncated value: 'some_value'.

The the information supplied in this error message and make any adjustments required on either the data , column width or data type.

If you are using SQL Server 2019 - there is the VERBOSE_TRUNCATION_WARNINGS  option - enabled through the ALTER DATABASE SCOPED CONFIGURATION

Read More on DBCC and trace flags

List SQL Server DBCC commands using trace 2520 – documented and undocumented commands