inserting or updating rows based on the lookup

[dsquestion]

Hi All,

I have the following scenario,

i need to insert or update the rows which is coming from the source based on the look up which will have the table name and column name.

for eg:

source :

id,value
------------
a1,35
a2,34

Lkp:

id,tablename,column name
-------------------------------
a1,dept,dept name
a2,emp,emp name

in the above example if the id matches it has insert the value in to the corresponding Table name and the column name.

Note :the table name and column name mentioned in the lookup will keep on changing

Comments appreciated.

[kumar_s]

HI,
One way would be, after lookup split and write into different file based on the name of the table.
Do a preprocessing in another job to pass the name of the table as a parameter to the job(if the metadata of all the tables are same).
Else pass files to the repective job which loads the table.

-Kumar

[dsquestion]

Thanks for the comments

In my case i need to consider the column names of the Table.The metadata of the table varies.

Can you tell me whether we can assign the stage variable value to a job parameter?

comments apperciated

[kcbland]

There is no dynamic SQL capability. You'll need to use a stored procedure to do what you wish.

[kwwilliams]

I know that this is a server posting, but did want to mention that this would be possible using RPC in a parallel job.

[chulett]

RCP.

[dsquestion]

Hi kwwilliams,

Can you please tell how do that in the parallel version ? we have the same scenario for a parallel version project.

Your solution will be really helpful for us.

Thanks in advance

[kwwilliams]

I am going to retract my statement from earlier. I believe in either server or parallel you are going to have to be use a stored procedure to get your work done. I created a job that used the following insert statement:

INSERT
INTO
ORCHESTRATE.TABLE_NAME
()
VALUES
()

That would be the only way outside of a stored proc to go dynamic. The problem I had when i tried to run the job was that it validated the sql first. It cannot validate the table name that I have sent to it because it does not exist. This was a case where in my mind I thought it was possible, but the actual implementation does not work. Sorry about the confusion. Good Luck.