Load multiple tables
Moderators: chulett, rschirm, roy
Load multiple tables
Hi All,
I have a dataset which looks like the following
Name Value
A 1
B 2
A 45
A 23
C 17
B 12
C 32
A 21
C 65
I need to load A,B and C table. This three table has the same structure.
Please find the A table structure with the data.
Sno Value create_dt end_dt current_flag
1 1 1/31/2005 1/1/9999 Y
2 45 2/1/2005 1/1/9999 Y
3 23 2/2/2005 1/1/9999 Y
4 21 2/3/2005 1/1/9999 Y
I need to create the multiple instance which will read the dataset and pick the Name as invocation id and load the respective table with its values.
This job should populate 3 tables A,B and C
Dataset-------->xfm--------------->target table(A,B,C)
Please let me know is this possible with multiple instance job.
Thanks
Suri[/code]
I have a dataset which looks like the following
Name Value
A 1
B 2
A 45
A 23
C 17
B 12
C 32
A 21
C 65
I need to load A,B and C table. This three table has the same structure.
Please find the A table structure with the data.
Sno Value create_dt end_dt current_flag
1 1 1/31/2005 1/1/9999 Y
2 45 2/1/2005 1/1/9999 Y
3 23 2/2/2005 1/1/9999 Y
4 21 2/3/2005 1/1/9999 Y
I need to create the multiple instance which will read the dataset and pick the Name as invocation id and load the respective table with its values.
This job should populate 3 tables A,B and C
Dataset-------->xfm--------------->target table(A,B,C)
Please let me know is this possible with multiple instance job.
Thanks
Suri[/code]
Re: Load multiple tables
The approach you have outlined is correct and will work. Parametrize the table name and that's it !!
In situations where say, your table structures(columns) vary, even then you can parametrize the insert/update statements in the target table stage and make do with just a single job. :D
In situations where say, your table structures(columns) vary, even then you can parametrize the insert/update statements in the target table stage and make do with just a single job. :D
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
Is the number of tables fixed, that is 3. If it is why are you not thinking of following job design.
Code: Select all
__________________ Target table A
|
|
Dataset ------->xfm ---------------------> Target table B
|
|__________________ Target table C
Assume everything I say or do is positive
As sud already noted, stick to your design. Make the job multiple instance and send the table name as a parameter. Within the transformer use the instance name to filter the records and send the correct records for a particular table.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Hi All,
Thanks a lot for all your suggestions.
I'm using the multiple instance job to load multiple dimensions.
Now I need to extract data from source using parameter table to get the MAX_DATE and MAX_SEQ_NO.
Extract SQL looks like below:
SELECT
*
FROM
#$SCHEMA_NAME#.#SrcTblNm#
WHERE ATTRIBUTE_NAME = '#DSJobInvocationId#'
AND Update_DT >= (select max_date from PARAM_TABLE where Tgt_Table = '#DSJobInvocationId#')
I have PARAM_TABLE from where I get the date and sequence number of the jobs last run.
My job will pick date and sequence number from parameter table and there is a sequence generator in the job which will add one to sequence number and generate the sequence number for all the records for that run.
Here max_date is VARCHAR in PARAM_TABLE.
Update_DT column is date column.
The SQL should look like below but the problem is with TO_DATE function where I give the first arugument in qutotaion.
SELECT
*
FROM
#$SCHEMA_NAME#.#SrcTblNm#
WHERE ATTRIBUTE_NAME = '#DSJobInvocationId#'
AND Update_DT >= TO_DATE('(select max_date from PARAM_TABLE where Tgt_Table = "#DSJobInvocationId#")','DD-MON-YYYY')
So I need to do type conversion on Max_date using to_date function.
Can someone help me how to resolve the above problem in the sql..
Thanks
Suri
Thanks a lot for all your suggestions.
I'm using the multiple instance job to load multiple dimensions.
Now I need to extract data from source using parameter table to get the MAX_DATE and MAX_SEQ_NO.
Extract SQL looks like below:
SELECT
*
FROM
#$SCHEMA_NAME#.#SrcTblNm#
WHERE ATTRIBUTE_NAME = '#DSJobInvocationId#'
AND Update_DT >= (select max_date from PARAM_TABLE where Tgt_Table = '#DSJobInvocationId#')
I have PARAM_TABLE from where I get the date and sequence number of the jobs last run.
My job will pick date and sequence number from parameter table and there is a sequence generator in the job which will add one to sequence number and generate the sequence number for all the records for that run.
Here max_date is VARCHAR in PARAM_TABLE.
Update_DT column is date column.
The SQL should look like below but the problem is with TO_DATE function where I give the first arugument in qutotaion.
SELECT
*
FROM
#$SCHEMA_NAME#.#SrcTblNm#
WHERE ATTRIBUTE_NAME = '#DSJobInvocationId#'
AND Update_DT >= TO_DATE('(select max_date from PARAM_TABLE where Tgt_Table = "#DSJobInvocationId#")','DD-MON-YYYY')
So I need to do type conversion on Max_date using to_date function.
Can someone help me how to resolve the above problem in the sql..
Thanks
Suri
You are converting something that is already a DATE to a DATE. Simplify your SQL:
And you need to use single quotes, not doubles.
Code: Select all
SELECT *
FROM #$SCHEMA_NAME#.#SrcTblNm#
WHERE ATTRIBUTE_NAME = '#DSJobInvocationId#'
AND Update_DT >= (select max_date from PARAM_TABLE where Tgt_Table = '#DSJobInvocationId#')
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Why?
Ok...
I'm assuming the date mask is correct from your previous post, you'd need to verify.
![Confused :?](./images/smilies/icon_confused.gif)
Code: Select all
SELECT *
FROM #$SCHEMA_NAME#.#SrcTblNm#
WHERE ATTRIBUTE_NAME = '#DSJobInvocationId#'
AND Update_DT >= (select TO_DATE(max_date,'DD-MON-YYYY') from PARAM_TABLE where Tgt_Table = '#DSJobInvocationId#')
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers