Hello All
In my job i have pull data from files and load into oracle stage.
Loading should be pure inserts.
table structure
col1 key char
col2 key char
col3 decimal
Data with same key value neither be inseted not updated. Job should throw an error. In server jobs i have done this by selecting only
"Insert rows witout clearing" option in OCI stage. If duplicate data is tried to inserted, unique constraint is thrown.
But in parallel, if i select "insert and then update option", no unique contstraint is thrown. instead already existing record is updated.
How can i implement INSERT only functionality in OCI stage of parallel edition 8X
oracle update&insert
Moderators: chulett, rschirm, roy
-
Vikas Jain
- Participant
- Posts: 15
- Joined: Tue Dec 13, 2005 12:38 am
Re: oracle update&insert
Hi,
There are two ways to do this, depending upon what you want to achieve.
1> If you only want to insert the records with new key values , and no updates if the non key column has changes, then use 'Upsert' Method and in Upsert Mode use 'User-defined Update only', and write the insert SQL in the Update SQL clause.
This will only insert the new records
2> If you also want to update the record with changes in non key column, then use Change Capture stage, check out which are update records, and which are pure inserts, and then use a transformer stage to split the insert records and update records and follow step 1 for both. For insert only use SQL to insert in the Update SQL clause whereas for Updates use SQL to update in the Update SQL clause
Hope this helps, else let me know if you need further details.
~Vikas~
There are two ways to do this, depending upon what you want to achieve.
1> If you only want to insert the records with new key values , and no updates if the non key column has changes, then use 'Upsert' Method and in Upsert Mode use 'User-defined Update only', and write the insert SQL in the Update SQL clause.
This will only insert the new records
2> If you also want to update the record with changes in non key column, then use Change Capture stage, check out which are update records, and which are pure inserts, and then use a transformer stage to split the insert records and update records and follow step 1 for both. For insert only use SQL to insert in the Update SQL clause whereas for Updates use SQL to update in the Update SQL clause
Hope this helps, else let me know if you need further details.
~Vikas~