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.
Oracle enterprise stage.
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
Oracle enterprise stage.
Regards,
Kenny
Kenny
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,
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,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
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,
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,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am