Delete existing record before insert
Moderators: chulett, rschirm, roy
Delete existing record before insert
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.
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.
Re: Delete existing record before insert
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
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
haisen wrote: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.
Thanks & Regards,
Shobhit Jauhari
Shobhit Jauhari
N.B. - you cannot "update" anything in either sequential or dataset stages. It is all-or-nothing.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Re: Delete existing record before insert
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).
haisen wrote: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.
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 - 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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia