All Forums Tools
andydoorey 35 posts Joined 05/09
23 May 2014
TPT - Can you load from a file containing multiple record types?

If we have a file which contains multiple record types, each with a different number of fields and record length but identified by the value in the first field, is it possible to load this using TPT?
A simple example would be a file with a header and trailer, eg:














Could we load the date from record type 01 to STG_HEADER, the ID and username in record type 10 to STG_USER_DETAIL, and the record count in record type 99 to STG_TRAILER?


Our files are more complex than this, but I want to know whether this is possible before I look at splitting the file using operating system commands.

feinholz 1234 posts Joined 05/08
23 May 2014

First of all, every record must adhere to a single schema. That is the most important rule to remember.
If the data is in delimited format, then we have a feature whereby short rows (rows with less fields than columns in the schema definition) will have NULL columns added to the end. And if a row has too many fields, then we can truncate the extra ones.
In the APPLY-SELECT statement, in the APPLY section, you can add CASE-WHEN logic so basically do this:
INSERT INTO TABLE_A( col-list );
INSERT INTO TABLE_B( col-list );
You will have to check the documentation for the exact syntax, but it should work.


andydoorey 35 posts Joined 05/09
27 May 2014

In this file each record type will have it's own schema.  In the above example record type 01 has 2 fields:
F1 - record_type  - byteint
F2 - created date - date
Record type 10:
F1 - record_type - byteint
F2 - person_id - char(4)
F3 - person_name - varchar(99)
Record type 99:
F1 - record_type - byteint
F2 - record_count - integer
Previously I would have used unix to split the file into 3, one for each record type, then used fastload to load to staging tables, then SQL to insert/update the final tables.
I'm now working on a site that is completely new to teradata so would like to use TPT where possible, rather than using fastload or multiload.  Ideally we would use TPT to run all the processing rather than use operating system specific pre-processing.  Is there any way of doing this in TPT?
I was wondering if you could do this using a filter operator.  i.e. take the file, define it as one single varchar field, split it into 3 data streams based on the first 2 characters of the record, then define each of these streams using a different schema as above.  I'm trying to work out from the manuals (TPT user guide and TPT reference) if this is possible, but there aren't any examples of anything like this.  Has anyone ever done this, or have any examples of anything similar?

feinholz 1234 posts Joined 05/08
27 May 2014

TPT does not support multiple schemas in a single input file.


You must sign in to leave a comment.