I am somewhat new to pervasive sql and I was wondering if I can run a series of sql statements as a transaction. IE I am trying to duplicate a few records in a database with new keys and then delete the old record but only if all the inserts worked properly
insert into xtable (columna, columnb) select '12345', columnb from xtable where columna = '23456'
insert into ytable (columnd, columne, columnf) select '444444', columne, columnf from ytable where columnd = '33333'
delete from xtable where columna = '23456'
delete from ytable where columnd = '33333'
if any of those fail I want to rollback the transaction.
Any thoughts on how I would do this?
thanks in advance..