Oracle Inserts
Moderators: chulett, rschirm, roy
Oracle Inserts
Hello
I have a requirement where i will get the flat file (app 2-3 million records) and i need to insert the data to oracle Database. I am Seq File stage, transformer and Oracle Enterprise stage for this. which is the best way to load the data to Oracle database. should i use load as write method or Upsert as write method. If i use upsert will it be both inserts and updates? Also i need to check for each record if it already exists in the database based on some key and if the record exists then i need to reject it to a file and if not i need to insert that record in the database. This check has to be done for every record and i need to commit all the records ewhich are inserted at the end of the job.
Thanks for your help
I have a requirement where i will get the flat file (app 2-3 million records) and i need to insert the data to oracle Database. I am Seq File stage, transformer and Oracle Enterprise stage for this. which is the best way to load the data to Oracle database. should i use load as write method or Upsert as write method. If i use upsert will it be both inserts and updates? Also i need to check for each record if it already exists in the database based on some key and if the record exists then i need to reject it to a file and if not i need to insert that record in the database. This check has to be done for every record and i need to commit all the records ewhich are inserted at the end of the job.
Thanks for your help
-
- Participant
- Posts: 152
- Joined: Tue Jan 13, 2009 8:59 am
Re: Oracle Inserts
if the record already exists on a key, do you want to update the record (in the table) with the new one or you want to reject that record???
-
- Participant
- Posts: 152
- Joined: Tue Jan 13, 2009 8:59 am
-
- Participant
- Posts: 152
- Joined: Tue Jan 13, 2009 8:59 am
you can use WRITE METHOD TO "load" if you just want to insert.
but if you want to reject the records those exists, you can't have reject records tapping option in the ORACLE stage( in LOAD mode).
you may have to do it in your job seperately and reject the existing records before you load to the database.
but if you want to reject the records those exists, you can't have reject records tapping option in the ORACLE stage( in LOAD mode).
you may have to do it in your job seperately and reject the existing records before you load to the database.
-
- Participant
- Posts: 152
- Joined: Tue Jan 13, 2009 8:59 am
-
- Participant
- Posts: 152
- Joined: Tue Jan 13, 2009 8:59 am
Well, that "load" option invokes sqlldr, so unless you can force it to do a conventional (DIRECT=FALSE) load you won't have any control over commits. If you've screened out any non-inserts then UPSERT may be an option as there are environment variables to control commits there.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Hello chulett,
As you said, after lookup against the target table i can send the failed records ( which are only inserts) to a database where i can use UPSERT method to insert records into database. I know there two environmental variables that control the commits. But wondering what are the values that i need to give if i want to commit after successful insertion and rollback if the job fails.
As you said, after lookup against the target table i can send the failed records ( which are only inserts) to a database where i can use UPSERT method to insert records into database. I know there two environmental variables that control the commits. But wondering what are the values that i need to give if i want to commit after successful insertion and rollback if the job fails.
I'm not sure you need to set both as I believe one overrides the other but yes - zero should mean only on successful completion. I'm unsure what the defaults are when those are not set, however... perhaps leaving them unset means the same thing?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers