Variable length records loading to multiple tables

[RaviReena]

I have a flat file with varaible length fields and records with a field delimiter of ^ symbol, I need to identifyand load them to different tables. based on the first two fields i need to identify which table the record goes and then use third and fourth fields as key fields for record insert/update in a table.

Anyone can suggest me what is the best approach to handle this situation in teradata loading environment.

Thanks in Advance.

[chulett]

So... you always have four (and only four) fields in each record? Or the first four are the key fields and there can be a variable number of fields after them per type? And out of curiousity, are you wanting to build one job to handle all record types and target tables or one job per record type / table? How many different tables are we talking about here anyway?

[RaviReena]

We have 48 different types of records in the same file. first two fields of each record identify which table to load the data and next two fields are the key fields.So from third field to last field of the record goes to table.

Maximum possible fields out of different 48 recrord types are 138.so we need to to load the data to 48 different tables and prefer to do in single job.

[ray.wurlod]

Cool. Create a Switch, Filter or Transformer stage with 48 outputs.

[Sainath.Srinivasan]

Options
1.) You can use CFF stage
2.) job1: Write output in a file with one extra field - the output tablename
job2: Use another job to 'grep' records of specific table and load
job3: Create sequencer to run job2 48 times - once for each table
3.) job1: Flatten the structure to an ALL column format
job2: Load required columns in target table

These are generic. You can tailor to suit your requirement.

[RaviReena]

I would prefer the third option,How we can flatten the structure to ALL column format?

[Sainath.Srinivasan]

In simple terms, create the output with all possible columns and map the source.

[Saad]

Well you can make use of Multiload multiple layouts in this case. You can define all the different layouts in a single script and based upon the value of first 2 characters jump to different labels in the script.

One of the limitations using multiload is that import task can do multiple data insert, update, and delete functions on up to five different tables or views. So in your case where you've 48 different tables, you might have to create multiple scripts for this activity.

I hope it helps

Regards,

Saad

[RaviReena]

Thank you everyone for input.

[ray.wurlod]

When you mark a thread as Resolved it is customary to make a post indicating how it was resolved. This assists future searchers.

[RaviReena]

We used option 2 of grep the records by filter property and load it to a table by running the job 48 times.

[RaviReena]

We used option 2 of grep the records by filter property and load it to a table by running the job 48 times.