Comparing Character Fields in SQL T-SQL

In Uncategorised by jayden.macrae

There are times when you may be inserting data from one table to another and you get the message:

String or binary data would be truncated

This is often because the data in the source table is too large for the data in the destination table. I’ve created the below script to quickly identify what field may be the culprit. Change the from and to table variables to represent the names of the tables you want to compare.


/* Dynamic script to compare two tables' character fields. Useful when getting message that string or binary data will be truncated
   or if you set the from and to tables to be the same, can show how much data is actually being used in text fields compared
   to their size.
 */

/* dynamic unpivot adapted from: https://dba.stackexchange.com/questions/48393/passing-column-names-dynamically-to-unpivot
 * thanks to https://stackoverflow.com/questions/19055902/unpivot-with-column-name for identifying how to get the column name into the result (duh)
 */

/* Setup variables below:
 * from tables should be where data is coming from - the table must contain data for this query to work
 * to tables should be where data is being inserted to
 */

declare @schema_name_from   nvarchar(max) = 'dbo'
declare @table_name_from    nvarchar(max) = 'FromTable'   /* this table must have data to be able to perform the comparison */
declare @schema_name_to     nvarchar(max) = 'dbo'
declare @table_name_to      nvarchar(max) = 'ToTable'

DECLARE     @colsUnpivot AS NVARCHAR(MAX),
            @colsMaxLength as nvarchar(max),
            @sql  AS NVARCHAR(MAX)

/* We want to form a list of columns names, one separated purely by commas, and the second applying the function we want to identify the maximum size of the data contained with each column */
select
    @colsUnpivot = string_agg(quotename(column_name), ',')
from
    (
        select
            distinct column_name
        from
            INFORMATION_SCHEMA.COLUMNS 
        where
        (
            table_name = @table_name_from
            and
            TABLE_SCHEMA = @schema_name_from and DATA_TYPE in ('nchar','nvarchar','varchar','char')
        )
        OR
        (
            table_name = @table_name_to
            and
            TABLE_SCHEMA = @schema_name_to and DATA_TYPE in ('nchar','nvarchar','varchar','char')
        )
    ) x

/* In this query, we must caset the result for each column to integer, otherwise the result comes out as the 
 * base datatype. It is then not possible to combine varchar(max) with varchar(n). By casting to integer
 * we get the counts coming through as integer, and we don't suffer the same issue.
 */
select
    @colsMaxLength = string_agg(quotename(column_name)+'=cast(isnull(max(len('+quotename(column_name)+')),0) as integer)', ',')     
from
    INFORMATION_SCHEMA.COLUMNS
where
    table_name = @table_name_from 
    and
    table_schema = @schema_name_from
    and
    data_type In ('varchar','nvarchar','char','nchar')

/* This SQL gets the data size in the 'from' table and unpivots it so it can be compared
 * to the column definitions returned from INFORMATION_SCHEMA.COLUMNS table. */

select @sql = '
/* calculate the data sizes within the from table */
with source_wide as (
SELECT
'+@colsMaxLength+'
from '+quotename(@schema_name_from)+'.'+quotename(@table_name_from)+'
),
/* unpivot the data from wide to long format */
source_long as (
    select SourceColumnName = d, SourceSize = data
    from source_wide
    unpivot(data for d in ('+@colsUnpivot+')) as unpvt
),
/* get the column definitions in the to table, in long format */
target_long as (
    select TargetColumnName = column_name, TargetSize = CHARACTER_MAXIMUM_LENGTH 
    from INFORMATION_SCHEMA.COLUMNS 
    where table_name = '''+@table_name_to+''' and table_schema = ''' + @schema_name_to + ''' and data_type In (''varchar'',''nvarchar'',''char'',''nchar'')
),
/* generate calculated fields */
output as (
select
    *,
    Status = case when SourceSize > TargetSize then ''Source too large'' else ''OK'' end,
    SizeDifference = TargetSize - SourceSize
from 
    source_long S
    full outer join
    target_long T on S.SourceColumnName = T.TargetColumnName
)
/* final output */
select * from output order by Status desc
'
print @sql
exec sp_executesql @sql