I have a similar question. I have a TPT that imports from a flat file. It works fine but I import the data as varchar, followed by a BTEQ script to cast a few of the fields into correct datatype into a second table. I'd like to remove this 2nd step and apply the cast on the import itelf. I'm not sure how to do this. Below is an excerpt from my TPT script. The REC_CNT field is integer data and actually loads successfully as is. The RUN_DATE field needs to be cast as timestamp, using CAST (RUN_DATE AS TIMESTAMP(0) FORMAT 'YYYYMMDDbHH:MI:SS') but I don't know where to put this?
Create the target table with Timestamp as data type, instead of VARCHARs. In the TPT schema definition, define all varchars which match the data in the flat file. You ca
Something like this:
DEFINE SCHEMA SCHEMA1
Target table where the data will be loaded, will have the actual datatypes like this:
CREATE TABLE TABLE1(
RUN_DATE TIMESTAMP(0) FORMAT 'YYYYMMDDbHH:MI:SS',
Note: This is untested, but should work.
If my answer is unclear, let me know. Please post the entire script (TPT, bteq etc) in future so that we can clearly understand what you are trying to do.
having problem loading the TEXT file where footer is also there, is it poosible to use two different schema ?
Data length implied by Data Schema (246) is not the same as record length (159)