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.
inserting or updating rows based on the lookup
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 26
- Joined: Thu Feb 03, 2005 1:05 am
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
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
-
- Participant
- Posts: 26
- Joined: Thu Feb 03, 2005 1:05 am
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
I know that this is a server posting, but did want to mention that this would be possible using RPC in a parallel job.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
-
- Participant
- Posts: 26
- Joined: Thu Feb 03, 2005 1:05 am
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
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.
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.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com