Third, I must be dumber than dirt because in tinkering with CDC I came to realize that the kill and fill method leaves me no where.CDC does not permit truncation and deletion indeed marks the target rows as deleted, but that means the source data has nothing in the target to compare it to and that the new source data is all there is! Adjustments and reversals also complicate the uniqueification.Īt the risk of the lash, I could try to create a 'covering' index to uniqueify the table, but the table is twelve million rows deep and 138 columns wide. When we get the source data on the SQL side, trying to uniqueify the table using the most important columns (claim number, date of service, date submitted, member, provider, diagnosis, procedure) consistently fails on duplicates because a provider can decide to rebill for the same exact event. The stewards of the accounting system (Finance and Operations) assert that it is not possible to have either constraint on the table in the accounting system. Second, NUTS! the requirement of a pk or unique index torpedo's my intention, as the source does not create uniqueness and attempts to create the minimum requirement, a unique index, consistently fail on the existence of duplicates. I would love to use it to change the extract from kill and fill to incremental.įirst, thank you Jason, Scott and Gail for your attention, time and advice.
Is it possible to use Change Data Capture against a table without a primary key? This is in contrast to replication, where the requirement for the existence of a primary key is made plain. In my reading I have seen many references to the LSN to decide when and what to record as changed, but I have not seen any refereference to the necessity of a primary key for Change Data Capture to work.
#Psequel cannot edit tables without primary key update
In reading about Change Data Capture, it seemed to me that column level insert update and delete are stored in helper tables that remember the before and after content of each column tracked.
Because of the expense of modifying the source system, there is enormous inertia/resistance to injecting a primary key at the source, so kill & fill it stays. The extract is copied to text in Unix and FTP'd to Windows, where the file is loaded into SQL Server by kill & fill. The system does not have a primary key because transactions are managed through program code. Or can it record before and after column changes based on the LSN only?Īn extract from a file based legacy accounting system is performed every night.
0 kommentar(er)
