string or binary data would be truncated. in sql server

Question

How to overcome this?

I have insert statements for large tables. there were lot of columns in those tables.

Is there any simple way to find the column which is causing above error?

Answer 1

The simplest way is to increase the length of the column which is giving you the error "string or binary data would be truncated. in sql server".

Now to find which column is giving that error you can check How to find what column caused the String or binary data would be truncated message

First thing we need to know is what the columns are in the table that are (n)chars 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

That was easy, now we want to know the max 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)

That code basically creates 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 in 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 is what the max length of the column itself is in the table that we want to insert into 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 will again do this dynamically 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 the 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 for Col3 will cause the 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

Answer 2

I believe you already know that this is related with inserting a string value into a column with less size You can query table columns from know table names and compare them.

select
    OBJECT_NAME(object_id),
    name,
    max_length,
    precision,
    scale
from sys.columns where object_id in (
    select object_id from sys.tables where name in ('tbl','Emp')
)