jvoconnell
asked on
string or binary data would be truncated
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?
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?
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'
insert into Table2(f1,f2,f3)
select f1,f2, cast(f3 as varchar(4)) from Table1 where f3 <> 'unknown'
>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.
>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.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. Thanks for taking the time.
If it wasn't part of the data and if you are 100% sure, it could be some other column or some other data;