Error in Microsoft SQL Server: "String or binary data would be truncated"

Is there an effective way to identify the responsible field?

If we think of a way that is flexible enough to solve the problem in the multiple ways of obtaining a recordset, that is, from a table, or from multiple, with combined columns, or dynamic queries or SPs or Functions, or through linked servers. The answer is no.

I can yes, in any way, propose some possible methodologies:

Try and failure

It is the most flexible and tedious mechanic, you have a query that returns 10 columns, and that triggers an error of this type. The procedure is "divide and conquer", that is, you repeat the query but only inserting the first 5 columns, if you get an error, you repeat the procedure reducing the columns by half, if you don't get an error, you try with half of the next 5 columns and so on until the problem column is isolated.

Select * into

Some ways of getting a recordset allow you to do a select * into <tabla temporal>, which basically involves dynamically creating the table. For example, suppose this query is sure to give an error:

DECLARE @Tabla TABLE (
    name VARCHAR(40)
)

INSERT INTO @Tabla (name)
SELECT  name
    FROM master.sys.objects so 

Obviously you could have many more columns, the idea, however, is to insert this query into an automatically created table, and then yes, check the lengths. We take the original query and insert it directly into a temporary session:

SELECT  name
    INTO #TMP
    FROM master.sys.objects so

And now we simply check the maximum lengths of each column:

SELECT  MAX(LEN(name))
    FROM #TMP

And obviously we will have to compare these maximum values ​​with those of the final table where we want to insert the data.

You could do something like this:

I used the following table for this example:

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

This query shows for each column of the target table the name, the data type, the maximum length, the precision, the scale, if it can be null, etc (you can add other information that I omitted in this case). destination_table_name would be the name of your destination table (where you are going to insert the data). It will help you to compare later with the information you get from the source table.

SELECT 
 C.NAME AS COLUMN_NAME,
       TYPE_NAME(C.USER_TYPE_ID) AS DATA_TYPE,
       C.IS_NULLABLE,
       C.MAX_LENGTH,
       C.PRECISION,
       C.SCALE
FROM SYS.COLUMNS C
JOIN SYS.TYPES T
     ON C.USER_TYPE_ID=T.USER_TYPE_ID
WHERE C.OBJECT_ID=OBJECT_ID('nombre_tabla');

Departure:

Then from the previous query you can automatically generate queries on the source table.

For example: with this query you create a query (or you can create several) which you are going to execute later. In this case each varchar column of the table corresponds to a row, and also shows the length of the longest string in that column.

SELECT 
  'SELECT ''' + C.NAME + ''' AS nombreColumna, MAX(LEN(' + C.NAME + ')) AS maxLength FROM Persons UNION ' AS query
FROM SYS.COLUMNS C
JOIN SYS.TYPES T
     ON C.USER_TYPE_ID=T.USER_TYPE_ID
WHERE C.OBJECT_ID=OBJECT_ID('Persons') AND TYPE_NAME(C.USER_TYPE_ID) = 'varchar';

The output of this query is:

So you copy all those rows as a single query, replace the last UNION with a semicolon, and run it:

SELECT 'LastName' AS nombreColumna, MAX(LEN(LastName)) AS maxLength FROM Persons UNION 
SELECT 'FirstName' AS nombreColumna, MAX(LEN(FirstName)) AS maxLength FROM Persons UNION 
SELECT 'Address' AS nombreColumna, MAX(LEN(Address)) AS maxLength FROM Persons UNION 
SELECT 'City' AS nombreColumna, MAX(LEN(City)) AS maxLength FROM Persons;

The result would be this:

Returns for each varchar field of the source table the length of the longest string it contains.

So you can generate different combinations according to what you need.