Date: 04/20/05 (SQL Server)    Keywords: no keywords

Here's a select statement.

insert into eligstaging2(recordnum, emembno, elstnam, efstnam, eadrln1, eadrln2, ecitycd, estacod,
ezipcod, ebthdat, esexcod, eeffdat, ecommcd, etiercd, egrpnum, eexpdat, erecordnum, drecordnum)
select employees.recordnum, left(employees.emembno,11), left(employees.elstnam,15), left(employees.efstnam,15),
left(employees.eadrln1,25), left(employees.eadrln2,25), left(employees.ecitycd,15), left(employees.estacod,2),
left(employees.ezipcod,10), left(employees.ebthdat,8), left(employees.esexcod,1), left(employees.eeffdat,8), left(employees.ecommcd,2),
left(employees.etiercd,3), left(employees.egrpnum,16), left(employees.eexpdat,8),
eligstaging.erecordnum, eligstaging.drecordnum
from employees inner join eligstaging on employees.recordnum = eligstaging.erecordnum

employees and eligstaging2 are identical tables, except that eligstaging2 contains two extra fields at the end, erecordnum and drecordnum. Both are int fields both in eligstaging (the source table for these columns) and eligstaging2. In order to make absolutely sure that there's no possibility for truncation I don't know about, as you can see, I've enclosed every character field in the query in left() statements with the length of the column as the length argument.The only non-character column in employees is recordnum, which is also an int. The column lengths are correct; I've checked both takes five times, both against each other and against the query. I am 100% sure that those numbers are correct. Also, I'm not inserting new numbers into the int columns, I'm inserting numbers that are coming from other int columns, so there can't be any numbers that don't fit into int columns. (Actually, the largest number in the test file I'm using is 59, so that's defniitely not the problem.)

Can someone tell me where else string or binary data might be truncated and what to do about it, because I swear I've covered every base, and I'm still getting the error. Thanks.

Source: http://www.livejournal.com/community/sqlserver/24835.html