Oracle enterprise stage.

kennyapril · Post by **kennyapril** » Mon Jul 18, 2011 10:45 am

I am using oracle enterprise stage to load the tables(to insert new records and update the old records)
The properties I used in the target are
Insert SQL=insert into table name (column1,column2...)
table= tablename
UpdateSQL=Update tablename set column1=orchestratecolumn1......
upsertmode=auto generated update and insert
upsertorder=Insert then update
Write method=Upsert

First time when I run the job the records loaded are 30000, second time also same 30000 records get inserted

Please let me know the settings required so that only the new records insert and old ones update.

chulett · Post by **chulett** » Mon Jul 18, 2011 10:49 am

No setting here - the first action must fail for the second to happen, so you'll need a unique index over your key(s) to keep the duplicate inserts from happening.

jwiles · Post by **jwiles** » Mon Jul 18, 2011 11:02 am

You didn't mention any errors regarding constraint violations on primary keys, so I'm guessing that there is no key defined and/or no unique constraint on key values.

There are at least a few options:

1) Set the order to Update then Insert. The SQL will need some WHERE clause to properly identify the row to be updated, or at least flag the Key columns in your metadata grid. The same requirement as Craig mentions above is true here: the Update must fail in order for the Insert to happen.
2) Identify the rows to be updated within DataStage and send them to an OracleEE stage which performs only Updates (or Update the Insert), send the remaining rows (new records) to an OracleEE stage which performs only Inserts. You may need to use separate jobs to perform the actual inserts and updates.
3) Completely rebuild the table each time: Pull the table into DataStage, update the data within DataStage, then load the resulting data back to the database.

Regards,

kennyapril · Post by **kennyapril** » Mon Jul 18, 2011 11:47 am

Thanks for the Information.

Primary key was not defined earlier , now its defined.

If its only insert for a table then Do I need to use insert statement in the update also..as there is no insert only?

jwiles · Post by **jwiles** » Mon Jul 18, 2011 3:30 pm

If your data is properly identified and separated as update vs insert, the insert statement in the Update First option will never be called so it won't really matter.

One common scenario is to use the Load method of Insert (new) rows and Upsert (Update then Insert) method for the updating rows. You would probably want separate update and insert/load jobs in this scenario.

Regards,

chulett · Post by **chulett** » Mon Jul 18, 2011 3:43 pm

For insert only, the trick (I believe) is to use the "User-defined Update Only" option and then make your actual user-defined SQL an insert statement.

kennyapril · Post by **kennyapril** » Wed Jul 20, 2011 2:32 pm

Thanks Chulett.

I will follow the same for insert only as below and I assume that is the same for update only also just change in the user defined update statement.