Comparing Column Definitions, want to avoid an 8152 or 8115 - Truncation of Data or Arithmetic Overflow

We have an ETL process that occasionally gets an 8152 truncation of data or an 8115 Arithmetic overflow because the Receiving field is not large enough to accommodate the sending data. I have been tasked with taking all of our staging tables and comparing the column sizes to the transformation table column sizes. I have the table built and populated with 4 values: Staging table name, column name, Transformation table name, column name.

I ran a query to compare the length, precision, scale and type of the two columns. But the differences were overwhelming. Now I just want to zero in on those column differences where the receiving column is small.

So do I care if the field types are different? Do I care that the destination is a VARCHAR and the source is a CHAR, or NUMERIC and DECIMAL? We only have data types decimal, numeric, varchar, char, datetime, int.

Thinking that I could have the following WHERE clause, would this pick up where the receiving field was smaller or do I have to be concerned with different types or some other column definition?

WHERE length_dest < length_source or precision_dest < precision_source or scale_dest < scale_source

• Edited by TheBrenda 16 hours 6 minutes ago

Please post (1) codes to reproduce your issue, (2) queries that you need to execute during the ETL (in order to understand what and how you use the columns values),

>> So do I care if the field types are different?

In some cases yes

>> Do I care that the destination is a VARCHAR and the source is a CHAR

Again, in some cases yes. For example, The char type might includes padding (instead of "x" you have "x    ") while in the VARCHAR you probably expected not to have the padding... we need to understand your specific issue 

declare @T1 char(10) = 'x'
declare @T2 varchar(10) = 'y'
select @T2 = @T1 + @T2
select @T2
-- result 'x         '
-- there is no 'y' in the result since there is no place for the y
-- the padding of the @T1 is there

>> or NUMERIC and DECIMAL

I do not know about differences between the two, Other than the type_id values.

** Converting from int, smallint, tinyint, float, real, money, or smallmoney to either decimal or numeric can cause overflow. During the ETL process you might done some implicit conversion to smallint, tinyint, float, real for example. without seen the ETL process we have no information what you do but only general stories.

To add a few thoughts...

Decimal and numeric are exactly the same (except the name and id, of course). Provided they have the same scale and precision, of course.

Event between varchar you can have different behaviour depending on the ANSI_PADDING setting (whether or not to remove possibly trailing spaces). And, of course char is fixed length to trailing spaces are *added* to the full length. So, for these we cannot say what is right for you, only you can determine that.

For further thought, please post more specific (like this and that types).

I want to avoid an 8152 or 8115. Forget about the ETL, just say that I am doing a

Insert into table1 (field1)
 Select field2 from table2

So I built a table that has the following 4 values and i am querying sys.columns. field1 and field2 can only be the following data types decimal, numeric, varchar, char, datetime, int.

table1, field1, table2, field2

I want to build a WHERE statement that can catch possible truncation or overflow. Should this catch all the 8152 or 8115 situations?

WHERE (field1_Precision = 0 and field2_precision = 0 and field1_max_length < field2_max_length) -- both varchar
   or (field1_precision > 0 and field2_precision > 0 and (field1_precision < field2vprecision or field1.scale < field2.scale) -- both numeric
   or (field1_precision = 0 and field2_precision > 0 field1_max_Length < field2_precision)  -- varchar and numeric
   or (field1_precision > 0 and field2_precision = 0 and field1_precision < field2_max_length) -- numeric and varchar