Update and Insert using user defined sql in ODBC stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 11
- Joined: Sun Jan 30, 2005 11:42 pm
Update and Insert using user defined sql in ODBC stage
I need to update and insert using a user defined sql in ODBC stage.
It does not work and gives a warning.
If I run the update statement alone or Insert statement alone then it works. Any idea how to get to work both the statements at a time.
It does not work and gives a warning.
If I run the update statement alone or Insert statement alone then it works. Any idea how to get to work both the statements at a time.
Re: Update and Insert using user defined sql in ODBC stage
No you don't.sagarkulkarni_in wrote:I need to update and insert using a user defined sql in ODBC stage.
What's wrong with the two link approach that you already know works?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
One stage, two links.
Hashed File contains keys from target table, lookup determines existence of key in target table, which makes insert/update decision.
Code: Select all
HashedFile
:
: inserts
V --------->
--> Transformer ODBC
--------->
updates
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 11
- Joined: Sun Jan 30, 2005 11:42 pm
Re: Update and Insert using user defined sql in ODBC stage
There is nothing wrong in going with separate insert and update but wouldnt that increase one more job. That is on one job I will do update where as in other job insert.
Is it possible to have two links in one job...one which updates the database whereas other which inserts. I think thats nlt possible in the same job.
Is it possible to have two links in one job...one which updates the database whereas other which inserts. I think thats nlt possible in the same job.
Re: Update and Insert using user defined sql in ODBC stage
Read Ray's suggestion - this is easily accomplished in the one job. Have the one ODBC stage with 2 inputs, one for inserts and one for updates. Use a transformer with a hash lookup of the key values from the target table to determine whether a record exists and insert or update accordingly.sagarkulkarni_in wrote:There is nothing wrong in going with separate insert and update but wouldnt that increase one more job. That is on one job I will do update where as in other job insert.
Is it possible to have two links in one job...one which updates the database whereas other which inserts. I think thats nlt possible in the same job.
-
- Participant
- Posts: 83
- Joined: Sat Oct 28, 2006 6:25 am
Re: Update and Insert using user defined sql in ODBC stage
Also if you have single job with "upsert" option (update + insert) you may end up loosing processing time to check whether it is update or insert. Therefore I will sugesst you to go for two links instead of one.
ShaneMuir wrote:Read Ray's suggestion - this is easily accomplished in the one job. Have the one ODBC stage with 2 inputs, one for inserts and one for updates. Use a transformer with a hash lookup of the key values from the target table to determine whether a record exists and insert or update accordingly.sagarkulkarni_in wrote:There is nothing wrong in going with separate insert and update but wouldnt that increase one more job. That is on one job I will do update where as in other job insert.
Is it possible to have two links in one job...one which updates the database whereas other which inserts. I think thats nlt possible in the same job.
That is again based on the case by case.Also if you have single job with "upsert" option (update + insert) you may end up loosing processing time to check whether it is update or insert. Therefore I will sugesst you to go for two links instead of one.
Say for e.g., if you are bringing the data from a transaction table and you are quite sure of more inserts than updates or the other way around. In those cases it would be better to go with one link instead of two.
Success consists of getting up just one more time than you fall.
This is my personal opinion, so keep that in mind.
I've never seen a situation where 'one link' was better than two. Best, IMHO, to take the time to determine what needs to be done with each row and handle each accordingly. Those dual-action updates actions are evil.
I've never seen a situation where 'one link' was better than two. Best, IMHO, to take the time to determine what needs to be done with each row and handle each accordingly. Those dual-action updates actions are evil.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers