We help IT Professionals succeed at work.

string or binary data would be truncated

jvoconnell
jvoconnell asked
on
Experts,

The problem below has been coded to achieve the desired result. We are just looking for a definitive answer of why the error occurs.

We have a target table we are inserting into with a target column datatype of varchar(4).
We have a source table with the source column of datatype varchar(7).

In the source table all the records are 4 characters long except for one record which contains the value of "UNKNOWN".

We got the error that string or binary data would be truncated when running the insert. This made sense if one of the records being inserted was the record with the value of "UNKNOWN". That record wasn't explicitly excluded, but it wouldn't have been part of the data set being inserted. Why does the error get thrown?
Comment
Watch Question

AneeshDatabase Consultant
Top Expert 2009

Commented:
> That record wasn't explicitly excluded, but it wouldn't have been part of the data set being inserted.
If it wasn't part of the data and if you are 100% sure,  it could be some other column or some other data;
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
maybe you can try to cast your field:

insert into Table2(f1,f2,f3)
select f1,f2, cast(f3 as varchar(4)) from Table1 where f3 <> 'unknown'
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>We have a source table with the source column of datatype varchar(7).
>We have a target table we are inserting into with a target column datatype of varchar(4).
It doesn't matter what the values are,  a SSIS will always return a truncation warning when inserting a varchar(7) into a varchar(4).

>That record wasn't explicitly excluded, but it wouldn't have been part of the data set being inserted.
Doesn't matter.  SSIS requires a 'contract' between source and target as far as column data types, and it will NOT look at the source data and conditionally change that contract.

Author

Commented:
Thanks for the responses.

Jim,
This isn't via SSIS. But am I correct in assuming what you are saying is that even if there is a possibility of truncation, the error will be thrown even though in this particular instance none of the values would have been affected?
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Youll receiving a warning, not an error if the possibility exists, and an error (dependent on if you have SET ANSI_WARNINGS ON or OFF) if there is a value that goes over.

A vastly superior idea though is to resolve this so that the possibility doesn't exist, either by weeding out the offending rows, changing the value, and making sure that a varchar(4) gets pumped into a varchar(4).

Author

Commented:
Thank you. Thanks for taking the time.