Delete existing record before insert

[haisen]

Is there any way to delete existing record before insert into the database in a single job? There is a delete before insert in version 7, however I no longer see it in version 8.

The work-around I am doing now is to have a job to read from source and delete from target, and then another job to read from source again and insert into the target.

[199542]

Hi ,
which database stage are you using ?/

If you are using oracle enterprise stage then there are different options like

Load , Truncate , Create , Replace ......You can use anyone according to your

requirement .

[shobhitj]

Hi haisen,

Please clarify what is the target stage you are using.

For some information i can tell you that for different stages in target what has to be used for the problem specified above:-

Write : It will create the new file
Truncate : It will delete the records from the table & insert the new record in that table.
Replace : It will delete the structure of tha table & makes the table again & inserts the records in table.
Append : It will append the reocrds in the table & ot also touching the reocrds of the table which are present there.

ODBC Enterprise Stage :-
Write Method : Write
Write Mode : Truncate

Oracle Stage :-
Write Method : Load
Write Mode : Truncate

Dataset Stage :-
Update Policy : Overwrite

Sequential Stage :-
File Update Mode : Overwrite

Netezza Stage :-
Write Method : Write
Write Mode : Truncate

Let me know if you face some issues

Is there any way to delete existing record before insert into the database in a single job? There is a delete before insert in version 7, however I no longer see it in version 8.

The work-around I am doing now is to have a job to read from source and delete from target, and then another job to read from source again and insert into the target.

[ArndW]

N.B. - you cannot "update" anything in either sequential or dataset stages. It is all-or-nothing.

[shobhitj]

Yes in Dataset & sequential file it is the case that you canot update anything & when the job rinsit deletes the file & makes it in new format.
If the jobs gets aborted in between then there will be no file created in server

[sreddy]

Haisen

• As per my knowledge we can go for delete option only the target table has very few records.
  
  Why the people are not using delete rows and then insert records. Here operation is down record by record that is burden on the server also.
  Some time Target table is locked two operation at a time (Delete rows and Insert New records )
  
  Truncate is best option. In this truncate all the records at a time.
  For this best way is writing a script put in VI UNIX file. You put that file path at your job parameters Before Job Subroutine (ExecSH or ExecSH.Silent).

Is there any way to delete existing record before insert into the database in a single job? There is a delete before insert in version 7, however I no longer see it in version 8.

The work-around I am doing now is to have a job to read from source and delete from target, and then another job to read from source again and insert into the target.

[chulett]

sreddy - Truncate is the best option? Deleting individual records before inserting them again ("Replace existing rows completely") is not equivalent to truncating the table before inserting.

haisen - If that's really what you need to do, I would think you should be still be able to use the original stage rather than the 'enterprise' version of the same stage if that is your issue. As noted, it would help to know what stage / database we're talking about here, 7 versus 8, so that more 'targeted' advice can be given.

[haisen]

It is a transaction refresh to Oracle stage. We cannot do truncate nor replace as there is both new and amended records.

[haisen]

It is a transaction refresh to Oracle stage. We did not use truncate nor replace as there is both new & amended records.

[keshav0307]

so you want to insert, update and delete in a single oracle stage?

[haisen]

Yes, is there a way to do insert, update & delete all in a single job?

[sreddy]

Haisen


As per my knowledge PL/SQL is the only option to do all three in one jobs.

Yes, is there a way to do insert, update & delete all in a single job?

[keshav0307]

so you can go for Stored procedure option.