Page 1 of 1
dynamically updating the columns with dynamic columns change
Posted: Mon Jul 13, 2009 12:07 pm
by vijaydev
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
Posted: Mon Jul 13, 2009 2:11 pm
by jcthornton
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?
Posted: Mon Jul 13, 2009 2:32 pm
by vijaydev
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
Posted: Tue Jul 14, 2009 8:51 am
by jcthornton
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:
Code: Select all
update X
set y = xx.z
from xx
where X.a = xx.a
and xx.b = <value>
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.
Posted: Tue Jul 14, 2009 8:58 am
by Sainath.Srinivasan
Vijaydev,
Can you give some examples with pseudocode of how you expect your system to work.
Otherwise others will be just guessing and giving answers way beyond your requirement and imagination.