I am processing .csv files daily which has persons data. Every person has a unique ID.
My table has FileID and PersonUniqueID columns; it is working fine and saved to parsed table, i.e. with duplicate rows.
Now would like to copy the unique data to new tables which is used by other applications.
I can copy the data using simple SELECT using NOT EXISTS with DISTINCT clause but it I would need to track the history of existing PersonUniqueID.
i.e. would need to keep latest PersonUniqueID in main table and previous rows belongs to him, need to copy them to a HISTORY table.
Please let me know what the best approach to handle this case is.
I would thinking about to use LOOP to traverse PersonUniqueID one by one, but it will take too much time for millions of rows of data.