Hi,
I have an requirement, i will get one csv file with the below format
SrcTble SrcColum SrcColumVal TgtTble TgtColum1 TrgColumn1Val
A X 1 D XX 123
B Z 3 H JJ 678
TrgColum2 TrgColum2Val
YY 456
VV 000
based on the above file i need to update the target table columns dynamically becuase the table and columns will change and the
WHERE cluase constraint is based on the src table column value matches.
Can any one suggest me how to do develop this logic.
-kumar
dynamically updating the columns with dynamic columns change
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 79
- Joined: Thu Mar 22, 2007 4:58 pm
- Location: USA
From this source file, are you just looking to build and execute a single update statement for each row - with source and target tables accessible in the same statement?
Or do you need to dynamically select from one database and then dynamically update from another?
Or do you need to dynamically select from one database and then dynamically update from another?
Jack Thornton
----------------
Spectacular achievement is always preceded by spectacular preparation - Robert H. Schuller
----------------
Spectacular achievement is always preceded by spectacular preparation - Robert H. Schuller
jcthornton wrote:From this source file, are you just looking to build and execute a single update statement for each row - with source and target tables accessible in the same statement?
Or do you need to dynamically select from one database and then dynamically update from another?
I am looking for dynamically select from the csv file given columns ( any data type) and then dynamically update the specified tables and columns.
-Kumar
Vijay
-
- Premium Member
- Posts: 79
- Joined: Thu Mar 22, 2007 4:58 pm
- Location: USA
Kumar,
That was not the answer I wanted to hear. Rephrased, your question is:
How to build a generic job that can read from multiple tables and update multiple tables simultaneously - presumably without a consistent datatype for the column(s) to be updated.
This is an extremely difficult task and could probably be done better using other tools (Perl script perhaps). If the job could be simplified so that it was reading from your file and creating/executing multiple update statements, it would be much easier.
In the simpler case, you would be building a statement resembling:
The series of such update statements could be built straightforwardly in DS, and then using the looping capability at the sequence level, those statements could be executed.
Adding the random read - especially if it does have differing metadata for the necessary fields - takes this to a whole other order of magnitude for complexity. Still doable? Yes. Worth doing? Probably not. Maintainable? No.
That was not the answer I wanted to hear. Rephrased, your question is:
How to build a generic job that can read from multiple tables and update multiple tables simultaneously - presumably without a consistent datatype for the column(s) to be updated.
This is an extremely difficult task and could probably be done better using other tools (Perl script perhaps). If the job could be simplified so that it was reading from your file and creating/executing multiple update statements, it would be much easier.
In the simpler case, you would be building a statement resembling:
Code: Select all
update X
set y = xx.z
from xx
where X.a = xx.a
and xx.b = <value>
Adding the random read - especially if it does have differing metadata for the necessary fields - takes this to a whole other order of magnitude for complexity. Still doable? Yes. Worth doing? Probably not. Maintainable? No.
Jack Thornton
----------------
Spectacular achievement is always preceded by spectacular preparation - Robert H. Schuller
----------------
Spectacular achievement is always preceded by spectacular preparation - Robert H. Schuller
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom