Generating insert, update and delete statement from Change Data Capture data

Question

I am using Change Data Capture to capture the change of data from software application. I am trying to generate the SQL statements (insert, update, delete) from the data captured.

Is there any proper way to get this done?

Answer 1

The way I have is/which I have worked on is, get all the change records from CDC tables along with the action (update/delete/insert) and pass the batch of records to a stored procedure which accepts table type as a input parameter. In the stored procedure you can basically write a cursor/ group by action to perform the operation on destination table. This way you don't need to generate dynamic SQL queries and run it on data base and we have seen this as a very efficient way when compared to generating dynamic sql and run it on DB.