I have MANY tables in my database and then the corresponding tables in a HISTORY database (that contains records EVERY time a record is updated, not just the most recent update). I want to be able to run a query that will compare the current table to the HISTORY table and return ONLY the fields/values that have changed, it can return a records set with ALL the fields in it, but only the changed records would have values in them, the rest would be NULL. The two table structures are almost exactly the same (history table has a few different/more for history tracking purposes but all data fields are the same.
I would like this to be dynamic if possible so I can pass the function/stored procedure a table name and it will return the changed fields ONLY based off the table I pass it.
I am dealing with MILLIONS of records in the CURRENT table and many MILLIONS in the history table. I have a few ways I know I can do this but none of them are very efficient when dealing with the number of records (and number of tables) I need to set this up for.
If you need more details on what I am trying to do please let me know.