Timely insight needed.
Last month, the load of data was successful.
This month, there is the error - "String or binary data would be truncated"
It appears that the error is attributable to several instances of the actual lookup values in columns within the text file that should contain the lookup codes instead of the actual value.
For example, state code should be only two characters like "CA" instead of California.
Therefore, I modified the sql script, as displayed below, to only load records that only have valid data.
Was about to experiment with the use of "Datalength" instead of "len" to resolve the error messages. However, due to not resolving the errors within the last two hours, some insight may be helpful.
(Eventually, I will return the records with erroneous data back to the source department. However, my priority is to load the valid records as soon as possible and worry about the invalid records later...)
So far, the revised sql script does not work due to the following additional errors;
1. Incorrect syntax near LTRIM
2. Incorrect syntax near 'dup'
Any insight as to what revisions to the sql script below are necessary to resolve the errors? Is the use of "len" with RTRIM and LTRIM the culprit?
Last month, the load of data was successful.
This month, there is the error - "String or binary data would be truncated"
It appears that the error is attributable to several instances of the actual lookup values in columns within the text file that should contain the lookup codes instead of the actual value.
For example, state code should be only two characters like "CA" instead of California.
Therefore, I modified the sql script, as displayed below, to only load records that only have valid data.
Was about to experiment with the use of "Datalength" instead of "len" to resolve the error messages. However, due to not resolving the errors within the last two hours, some insight may be helpful.
(Eventually, I will return the records with erroneous data back to the source department. However, my priority is to load the valid records as soon as possible and worry about the invalid records later...)
So far, the revised sql script does not work due to the following additional errors;
1. Incorrect syntax near LTRIM
2. Incorrect syntax near 'dup'
Any insight as to what revisions to the sql script below are necessary to resolve the errors? Is the use of "len" with RTRIM and LTRIM the culprit?
Code:
INSERT INTO #validate_EquipmentData
SELECT t1.Equipment_Code,
CASE
WHEN RTRIM(LTRIM(t1.[Account_Number])) = ''
THEN Null
ELSE RTRIM(LTRIM(t1.[Account_Number]))
END as [Account_Number],
RTRIM(LTRIM(t1.Sequence_Number)),
CASE
WHEN len(RTRIM(LTRIM(t1.[Equipment_Flag]))) > 1
THEN Null
ELSE RTRIM(LTRIM(t1.[Equipment_Flag]))
END as RTRIM(LTRIM(t1.[Equipment_Flag])),
CASE
WHEN len(RTRIM(LTRIM(t1.[State_Code]))) > 2
THEN Null
ELSE RTRIM(LTRIM(t1.[State_Code]))
END as RTRIM(LTRIM(t1.[State_Code])),
CASE
WHEN len(RTRIM(LTRIM(t1.[Tracking_Number]))) > 15
THEN Null
ELSE RTRIM(LTRIM(t1.[Tracking_Number]))
END as RTRIM(LTRIM(t1.[Tracking_Number])),
CASE
when len(t1.[Sale_Date]) = 8
and isdate(t1.[Sale_Date]) = 1
and RTRIM(LTRIM(t1.[Sale_Date])) like '[0-9][0-9][0-9][0-9][0-1][0-2][0-3][0-9]'
then convert(date, t1.[Sale_Date], 112)
else cast(null as date)
end as [Sale_Date],
CASE
WHEN ISNUMERIC(RTRIM(LTRIM(t1.[Sale_Amount]))) = 0
THEN Null
ELSE RTRIM(LTRIM(t1.[Sale_Amount]))
END as [Sale_Amount],
CASE When RTRIM(LTRIM(t1.[Purchase_Zipcode])) LIKE '[0-9][0-9][0-9][0-9][0-9]' then RTRIM(LTRIM(t1.[Purchase_Zipcode]))
When RTRIM(LTRIM(t1.[Purchase_Zipcode])) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' then substring(RTRIM(LTRIM(t1.[Purchase_Zipcode])),1 , 5)
else null
end as t1.[Purchase_Zipcode],
dup.count_dups
from #Bureau1 t1
left outer join (SELECT [Equipment_Code]
, [Account_Number]
, [Sequence_Number]
, count(*) as count_dups
from #Bureau1
group by [Equipment_Code]
, [Account_Number]
, [Sequence_Number]
) dup
on dup.Equipment_Code = t1.Equipment_Code
and dup.Account_Number = t1.Account_Number
and dup.Sequence_Number = t1.Sequence_Number
