Update and Insert using user defined sql in ODBC stage

[sagarkulkarni_in]

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.

[chulett]

I need to update and insert using a user defined sql in ODBC stage.

No you don't.

What's wrong with the two link approach that you already know works?

[ray.wurlod]

One stage, two links.

Code: Select all

          HashedFile
               :
               :      inserts
               V     ---------> 
   -->    Transformer          ODBC
                     --------->
                      updates

Hashed File contains keys from target table, lookup determines existence of key in target table, which makes insert/update decision.

[loveojha2]

Why do you want to go the user defined sql way.

Please provide the sql then you are trying to accomoplish, may be we can think of an alternate solution.

[sagarkulkarni_in]

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.

[ShaneMuir]

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.

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.

[tagnihotri]

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.

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.

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.

[loveojha2]

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.

That is again based on the case by case.

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.

[chulett]

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.