Only inserts through Oracle Enterprise stage

[dspxlearn]

How about these options:

Code: Select all

Write method : Upsert
Upsert mode: User defined update only
Update SQL: Write just an INSERT SQL statement instead on UPDATE statements

Can you post what was your approach?

[Minhajuddin]

Hi,

Yeah, I get your point, but...

If so see, there were previous replies in this same post that, the SQL in Update placeholder would get executed only if the SQL in Insert placeholder fails. As per this, in out case all incoming rows would get inserted through the Insert SQL and the Update SQL will not get executed for any row. Then, why should we explicitly give the where condition in the Update SQL as 0=1 , when the Update SQL is not at all going to get executed.

Thanks.

In an "ideal" case What you say would happen.
But, if the insert fails for any reason (maybe that key already exists...) it would go to the "update" query and if you don't have a condition which is always false it would successfully update. So a condition which is always false is used in the update where clause.....

[dspxlearn]

As posted earlier, what would be the result if we use the below options?

Upsert: write method
Upsert mode: User defined update only
Update SQL: Writing just an INSERT SQL statement instead on UPDATE statements.

I tried it and its working. But not sure how it impacts performance compared to the other options for only inserts.

[vnspn]

My approach that I tried was,

Write Method : Upsert
Upsert Mode : Auto-generated Update & Insert
Insert SQL : <auto-generated Insert SQL>
Update SQL : <auto-generated Update SQL>