DSXchange

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.

sagarkulkarni_in wrote: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?

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

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.

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.

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.

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.

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:

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.

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.

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.

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.