Sometimes you get data from Excel or another system and you need to import that data into your tables. Some people will use an import wizard and all the columns will be nvarchar(255) in the import table
Here is an example of a table created by the import/export wizard based on an Excel file
CREATE TABLE [dbo].[Sheet1$] (
[emailaddress] nvarchar(255),
[Value] nvarchar(255),
[CategoryName] nvarchar(255),
[CategoryDescription] nvarchar(255),
[CategoryID] float,
[UserAdditionalInfoTextValue] nvarchar(255),
[F9] nvarchar(255)
)
Sometimes the data that you are importing won’t fit into your column and you get the helpful String or binary data would be truncated message.
Of course it doesn’t tell you which column it is, now you need to figure out which column and compare the tables
I will show you what I mean, first create these two tables
use tempdb
go
create table TestTrunc(
Col1 varchar(10),
Col2 varchar(15),
Col3 varchar(20),
Col4 nchar(3),
Col5 nvarchar(10))
Go
create table temp(
Col1 varchar(50),
Col2 varchar(50),
Col3 varchar(50),
Col4 nchar(10),
Col5 nvarchar(50))
GO
Now let’s insert some data into the temp table
insert temp
select '1234567890','12345678901234567890','bla','1234','123456789011111'
union all
select '1234567890111','1233','bla111','1234','123456111'
If we now try to insert the data from the temp table into the TestTrunc table it will blow up
insert TestTrunc
select * from temp
_Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated._
Yes, very nice….could you indicate which column it actually has a problem with?
Now the purpose of this post is to create code that you then can modify for your own needs and can use it to compare any two tables.
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')
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')
Here is the 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
And again, we will use a dynamic approach, we don’t know the real column names
declare @sql varchar(8000)
select @sql = 'select '
select @sql += '' + 'case when t.' + column_name + ' > tt.' + column_name
+ ' then ''truncation'' else ''no truncation'' end as '+ column_name
+ ','
from information_schema.columns
where table_name = 'MaxLengths'
and column_name <> '_col0'
select @sql = left(@sql,len(@sql) -1)
select @sql +=' from MaxLengths t
join TempTrunc tt on t._col0 = tt._col0 '
--select @sql -debugging so simple, a caveman can do it
exec (@sql)
Col1 Col2 Col3 Col4 Col5 ------------------------------------------------------------------------------------ truncation truncation no truncation truncation truncation
That is all there is to it, you can of course customize it by passing in the table names and make this part of your toolkit to quickly see where the problem is
Making it truly dynamic
Here is what it would look like with dynamic table names
declare @ImportTable varchar(100)
declare @DestinationTable varchar(100)
select @ImportTable = 'temp'
select @DestinationTable = 'TestTrunc'
declare @ImportTableCompare varchar(100)
declare @DestinationTableCompare varchar(100)
select @ImportTableCompare = 'MaxLengths'
select @DestinationTableCompare = 'TempTrunc'
declare @sql varchar(8000)
select @sql = ''
select @sql = 'select 0 as _col0 ,'
select @sql += 'max(len( ' + column_name+ ')) AS ' + column_name + ','
from information_schema.columns
where table_name = @ImportTable
and data_type in('varchar','char','nvarchar','nchar')
select @sql = left(@sql,len(@sql) -1)
select @sql +=' into ' + @ImportTableCompare + ' from ' + @ImportTable
--select @sql -debugging so simple, a caveman can do it
exec (@sql)
select @sql = ''
select @sql = 'select 0 as _col0, '
select @sql += '' + convert(varchar(20),character_maximum_length)+ ' AS ' + column_name + ','
from information_schema.columns
where table_name = @DestinationTable
and data_type in('varchar','char','nvarchar','nchar')
select @sql = left(@sql,len(@sql) -1)
select @sql +=' into ' + @DestinationTableCompare
--select @sql -debugging so simple, a caveman can do it
exec (@sql)
select @sql = ''
select @sql = 'select '
select @sql += '' + 'case when t.' + column_name + ' > tt.' + column_name
+ ' then ''truncation'' else ''no truncation'' end as '+ column_name
+ ','
from information_schema.columns
where table_name = @ImportTableCompare
and column_name <> '_col0'
select @sql = left(@sql,len(@sql) -1)
select @sql +=' from ' + @ImportTableCompare + ' t
join ' + @DestinationTableCompare + ' tt on t._col0 = tt._col0 '
--select @sql -debugging so simple, a caveman can do it
exec (@sql)
exec ('drop table ' + @ImportTableCompare+ ',' + @DestinationTableCompare )
To Do list
Here are some things for you to add…….
Now if you have worked with SQL Server for a while now, you might notice that what I did was not really what you would call defensive programming.
Can you tell what I forgot?
1) if the column name has spaces or other weird characters I am not taking care of that. To take care of that use the QUOTENAME function or put brackets around column_name
2) I also didn’t look at the schema the table is in, you can use the TABLE_SCHEMA column for that, and the same applies as for columns, use QUOTENAME to take care of schemas that have an invalid name.
3) LEN trims spaces, be aware of that otherwise you might still get truncation if inserting into a char column.
example
create table TestTrim(SomeData char(3))
go
declare @d char(5) = '12 '
select len(@d) as len ,datalength(@d) as datalength
--len datalength
--2 5
insert TestTrim
values (@d)
select *,len(SomeData) as len,datalength(SomeData) as datalength
from TestTrim
--SomeData len datalength
--12 2 3
4) The columns that have varchar(max) values
Create this table
create table TestTrim(SomeData varchar(max))
go
Now run this and observe character_maximum_length
select * from information_schema.columns
where table_name = ‘TestTrim’
As you can see the character_maximum_length is -1 for (max) columns, you need to take that into account
5) If your database uses a case sensitive collation then you need to take that into account with column and table names
That is it for this post, hopefully the code helps you to find out where the truncation occurs and if you do the 5 modifications like I suggested it should be pretty bullet proof
Bonus, use IIF instead of CASE.
If you are using SQL Server Denali CTP3 or up, you can use IIF instead of CASE. See A Quick look at the new IIF function in Denali CTP3 for more information about IIF
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
select
IIF(t.col1 > tt.col1,'truncation','no truncation') as Col1,
IIF(t.col2 > tt.col2,'truncation','no truncation') as Col2,
IIF(t.col3 > tt.col3,'truncation','no truncation') as Col3,
IIF(t.col4 > tt.col4,'truncation','no truncation') as Col4,
IIF(t.col5 > tt.col5,'truncation','no truncation') as Col5
from MaxLengths t
join TempTrunc tt on t._col0 = tt._col0
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
