Data versioning - writing to two tables in a single transaction

Hello, 

Generally the question, is best way to do versioning with Appian on to DB? 

We are considering the following approach for implementation. Have a single table, which stores versions of data in the same table. Example, if row {id:'1',name:"test name", version:"1"} is updated then you have two rows one - version 1 and another version 2. How can I carry out both update and insert through a single transaction in Appian? This is the method in general we want to go ahead with. If i want to send both the CDT to write to data store, i will need to manipulate the cdts upfront, like get the old row id if present, increment version, then update and then insert.

Any other approaches please let us know.

Thank you,

Harish