Originally posted by TedHolt
View Post
Your example does not show what you're doing with the transaction. But, let's say for example you're posting the transaction into another table called PROCESSED_TRANS. In that case, you can / should structure your code like this:
Code:
--Post rows not already posted insert into PROCESSED_TRANS ( list of columns ) select [matching list of columns] from TRANSACT T where not exists( select 1 from PROCESSED_TRANS PT where PT.TRANS = T.TRANS ) ; --Delete rows that reached the processed table delete from TRANSACT T where exists( select 1 from PROCESSED_TRANS PT where PT.TRANS = T.TRANS ) ;
Batch jobs, in particular, will greatly benefit from using SET based SQL techniques.
Leave a comment: