Search code examples
sql-servercdc

querying data from change data capture Tables

I created the CDC on many related tables, and then I move it to a permanent storage tables. but I need to query those tables to get all the records that were updated in the same transaction, say I have Person->Worker. When I update an entity 2 records will be added to each table, if many updates were done on those tables, I will end up with many rows

how I can connect the same record in the person to the record that was updated with it in the worker....

Thanks

Solution

  • I found the problem.....

    __$Start_lsn is a unique value between all the tables that have been updated with the same transaction, so it can be used as a primary-foreign key relation to connect tables.

    the problem with my tables was that the main table and the subtables were updated in two different transactions.

    Thanks