Variable length records loading to multiple tables
Moderators: chulett, rschirm, roy
Variable length records loading to multiple tables
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.
Anyone can suggest me what is the best approach to handle this situation in teradata loading environment.
Thanks in Advance.
Rao V
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
Rao V
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
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.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: