Find out which column would be truncated (data would be truncated)

Of the many requests to the team responsible for MS SQL Server from the user community, it should be at the top of the list. Here is just one example:

Please fix the "String or binary data would be truncated" message to give the column name

If you encounter the error in the production environment, it is very difficult to detect which column is responsible for the error, since the data in many cases comes from the UI layer.

In the development / testing environment, or for example loading information from a file, there are some alternatives, one of which is to access the information available in the sys.columns table and to compare, column by column, with the that you are trying to insert into the table.

Since you are running an insert, I assume that you have somehow already mapped the information between the data source and the target table.

Begin by creating a temporary table with the data you want to insert, it is important that the column names are the same as the table in which you want to insert.

In this example we will assume that you want to insert the information into the TableXPTO table and that it has the following structure:

CREATE TABLE TabelaXPTO
(
   Coluna1     NVARCHAR(250),
   Coluna2     NVARCHAR(250),
   Coluna3     NVARCHAR(250),
   Coluna4     NVARCHAR(250),
   Coluna5     NVARCHAR(250),
)


SELECT X1 AS Coluna1
      ,X2 AS Coluna2
      ,X3 AS Coluna3
      ,X4 AS Coluna4
      ,X5 AS Coluna5  
  INTO #TabelaTemporaria
  FROM Origem

SELECT 
WITH Destino AS 
(
    SELECT C.column_id ,
           ColumnName= C.name ,
           C.max_length ,
           C.user_type_id ,
           C.precision ,
           C.scale ,
           DataTypeName = T.name
      FROM sys.columns C
     INNER JOIN sys.types T 
        ON T.user_type_id = C.user_type_id
     WHERE OBJECT_ID = OBJECT_ID('TabelaXPTO')
),
DadosOrigem AS 
(
    SELECT C.column_id ,
           ColumnName= C.name ,
           C.max_length ,
           C.user_type_id ,
           C.precision ,
           C.scale ,
           DataTypeName = T.name
      FROM TempDB.sys.columns C
      INNER JOIN tempdb.sys.types T 
         ON T.user_type_id=C.user_type_id
      INNER JOIN tempdb.sys.objects O 
         ON o.object_id=c.object_id
      WHERE O.name = 'TabelaTemporaria'
)
SELECT * 
  FROM Destino D
  INNER JOIN DadosOrigem O 
    ON D.ColumnName = O.ColumnName
WHERE ISNULL(D.max_length,0) < ISNULL(O.max_length,999)  --> O tamanho da coluna na tabela de destino é inferir ao tamanho dos dados que pretende inserir. 

You may have to make some adjustments to the above code, as I am currently not on a computer running SQL Server.

Open the Sql Server provider, take the query and open it in the sql manager and try to run on the interactive. There he gives you the line that gave error.