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?
LVL 1
jvoconnellAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
> 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 ConsultantCommented:
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 HornMicrosoft SQL Server Data DudeCommented:
>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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jvoconnellAuthor 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?
Jim HornMicrosoft SQL Server Data DudeCommented:
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).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jvoconnellAuthor Commented:
Thank you. Thanks for taking the time.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.