We receive a data feed from our customers, and each time we get about the same scheme, although it can change on the client side, because they use a third-party application. When we receive data files, we import data into an intermediate database with a table for each data file (students, attendance, etc.). Then we want to compare this data with the data that we already have in the database for this client, and see what data has changed (either the column has been changed or the entire row has been deleted) from the previous run. Then we want to write the updated values or deleted rows to the audit table so that we can go back to see what data has been changed from the previous data import. We do not want to update the data itself, we want to record only what is different between the two data sets.Then we will delete all the data from the client’s database and import the data in the same way as from the new data files, without changing it (this directive was transferred and cannot be changed). The big problem is that I need to do this dynamically, since I don’t know exactly what scheme I am going to receive from our clients, since they can customize their tables. I need to be able to dynamically determine which tables are in the destination and their structure, and then look at the source and compare the values to see what has changed in the data.since I don’t know exactly what schema I’m going to get from our customers, because they can customize their tables. I need to be able to dynamically determine which tables are in the destination and their structure, and then look at the source and compare the values to see what has changed in the data.since I don’t know exactly what schema I’m going to get from our customers, because they can customize their tables. I need to be able to dynamically determine which tables are in the destination and their structure, and then look at the source and compare the values to see what has changed in the data.
Additional Information: There are no identifier columns in the source text, although there are several columns that can be used as a surrogate key that will constitute a separate row.
I would like to be able to do this in general for each table without having to enter hard code values, although I may have to do this for surrogate keys for each table in a separate look-up table.
I can use SSIS, SP, triggers, etc., depending on what makes sense. I looked at everyone, including tablediff, and no one seemed to have everything I needed, or the logic was starting to get extremely complex as soon as I hit them.
Of course, any concrete examples that any of these have already done, would be greatly appreciated.
, - , .