String or binary data will be ignored. in sql server

The easiest way is to increase the length of the column which gives you the error "string or binary data will be truncated. On sql server".

Now to see which column is giving this error you can check How to find which column caused String or binary data will be truncated message

First of all we need to know what are the columns in the table that are (n) characters or (n) varchar

select column_name
from information_schema.columns
where table_name = 'temp'
and data_type in('varchar','char','nvarchar','nchar')

      

        
        
        
      

    

Output:

column_name
————–
Col1
Col2
Col3
Col4
Col5

      

        
        
        
      

    

It was easy, now we want to know the maximum length of the data in each column

declare @sql varchar(8000)
select @sql = 'select  0 as _col0 ,'
select @sql +=   'max(len( ' + column_name+ ')) AS ' + column_name + ',' 
from information_schema.columns
where table_name = 'temp'
and data_type in('varchar','char','nvarchar','nchar')

select @sql = left(@sql,len(@sql) -1)
select @sql +=' into MaxLengths from temp'

--select @sql -debugging so simple, a caveman can do it

exec (@sql)

      

        
        
        
      

    

This code basically builds and runs the following

select  0 as _col0 ,
    max(len( Col1)) AS Col1,
    max(len( Col2)) AS Col2,
    max(len( Col3)) AS Col3,
    max(len( Col4)) AS Col4,
    max(len( Col5)) AS Col5 
into MaxLengths 
from temp

      

        
        
        
      

    

If we now look at the MaxLengths table, we will see the following

select * from MaxLengths

_col0   Col1    Col2    Col3    Col4    Col5
---------------------------------------------------
0   13  20  6   4   15

      

        
        
        
      

    

Next, to figure out what the maximum length of the column itself is in the table we want to insert in Run the following query

select character_maximum_length,column_name
from information_schema.columns
where table_name = 'TestTrunc'
and data_type in('varchar','char','nvarchar','nchar')

      

        
        
        
      

    

Result

character_maximum_length    column_name
--------------------------------------------
10              Col1
15              Col2
20              Col3
3               Col4
10              Col5

      

        
        
        
      

    

We'll do it dynamically again and insert the values ​​into another table

declare @sql varchar(8000)
select @sql = 'select 0 as _col0, '
select @sql +=   '' + convert(varchar(20),character_maximum_length)+ ' AS ' + column_name + ',' 
from information_schema.columns
where table_name = 'TestTrunc'
and data_type in('varchar','char','nvarchar','nchar')

select @sql = left(@sql,len(@sql) -1)
select @sql +=' into TempTrunc '

--select @sql -debugging so simple, a caveman can do it

exec (@sql)

      

        
        
        
      

    

Now we can see what we have in two tables

select 'TempTrunc' as TableNAme,* from TempTrunc
union all
select 'MaxLengths' as TableNAme,* from MaxLengths

TableNAme   _col0   Col1    Col2    Col3    Col4    Col5
-------------------------------------------------------------
TempTrunc   0   10  15  20  3   10
MaxLengths  0   13  20  6   4   15

      

        
        
        
      

    

As you can see, all columns except Col3 will cause a truncation problem Of course we want to do something like this, it will tell us which columns have truncation problems

select  case when  t.col1 > tt.col1 then 'truncation' else 'no truncation' end as Col1,
 case when  t.col2 > tt.col2 then 'truncation' else 'no truncation' end as Col2,
 case when  t.col3 > tt.col3 then 'truncation' else 'no truncation'  end as Col3,
 case when  t.col4 > tt.col4 then 'truncation'  else 'no truncation' end as Col4,
 case when  t.col5 > tt.col5 then 'truncation' else 'no truncation'  end as Col5
   from MaxLengths t
join TempTrunc tt on t._col0 = tt._col0

Col1        Col2        Col3            Col4        Col5
------------------------------------------------------------------------------------
truncation  truncation  no truncation        truncation      truncation

      

        
        
        
      

    

Source