forcing a truncate

I have a script that dumps data from several tables into one, where the
source tables are not always the same data length. They are all varchar but
some are 30 chars, some 255, etc. Below is my insert query, which errors
because it won't truncate the 255 character data into 30. Is there a simple
way to automatically truncate that data that doesn't fit? All source tables
are different, so I don't want to have to go through 10 or more fields to
determine what their length is.
insert into tblcontact (firstname, lastname, streetaddress,
organizationname, city, statecode,
postalcode, homephone, businessphone,
mobilephone, faxnumber, emailaddress, username,
datechanged)
(select r_firstname, r_lastname, r_address, r_organization, r_city,
r_state,
r_zip, r_phone_h, r_phone_o,
r_phone_m, r_phone_f, r_email, Username, change_date from SourceTable1
where contactid is null and ...[query truncated])
Thanks for your help.dew,
The problem is not so much the source tables, but the destination table.
Your SELECT statement indicates one source to one destination table and so
it's (reasonably) simple.
The simple answer is to use the LEFT function as such::
INSERT INTO tblcontact (firstname, lastname, streetaddress,
organizationname, city, statecode,
postalcode, homephone, businessphone,
mobilephone, faxnumber, emailaddress, username,
datechanged)
(select LEFT(r_firstname, 30), LEFT(r_lastname, 30), LEFT(r_address, 30),
LEFT(r_organization, 30), LEFT(r_city, 30), LEFT(r_state, 30), LEFT(r_zip,
30), LEFT(r_phone_h, 30), LEFT(r_phone_o, 30), LEFT(r_phone_m, 30),
LEFT(r_phone_f, 30), LEFT(r_email, 30), LEFT(Username, 30), LEFT(change_date
,
30)
FROM SourceTable1
WHERE contactid IS NULL
AND ...[query truncated])
This will work in the main, but, of course, the LEFT unstion only needs to
be used on those columns that are obviously (or likely) to have in excess of
30 characters at the source table.
Obviously, the other way is simply to alter the destination table(s) to
accomodate the larger size.
Hope this assists,
Tony
"dew" wrote:

> I have a script that dumps data from several tables into one, where the
> source tables are not always the same data length. They are all varchar b
ut
> some are 30 chars, some 255, etc. Below is my insert query, which errors
> because it won't truncate the 255 character data into 30. Is there a simp
le
> way to automatically truncate that data that doesn't fit? All source tabl
es
> are different, so I don't want to have to go through 10 or more fields to
> determine what their length is.
> insert into tblcontact (firstname, lastname, streetaddress,
> organizationname, city, statecode,
> postalcode, homephone, businessphone,
> mobilephone, faxnumber, emailaddress, username,
> datechanged)
> (select r_firstname, r_lastname, r_address, r_organization, r_city,
> r_state,
> r_zip, r_phone_h, r_phone_o,
> r_phone_m, r_phone_f, r_email, Username, change_date from SourceTable1
> where contactid is null and ...[query truncated])
> Thanks for your help.
>
>