I'm planning on implementing a Change Data Capture on one of the files, we get. We get this file every week.
This file is a fixed length file and it gets loaded into our database. Since the volume of the file is large, we are planning on doing a full load for the first time, and the following load on wards, use the incremental load. We have analyzed the Merge statement for the Type-2 changes and wanted to implement it.
Now, the issue i got is that, this fixed length file may get new fields every month.
How can we utilize the merge command with respect to the schema changes and not loose any of the CDC data captured till then?