I have a temporal table that uses a natural primary key. This table stores a CSV file that was imported, it's a history table to keep track of every import. I starting to see some duplicates (in the CSVs I import) of the primary key field and would like to keep these records without loosing the functionality of the history table. I am currently manually removing rows from the CSV before import.
Is it possible to have a primary key consisting of a natural and surrogate key? Natural key is already used and an auto-incrementing key that only increments if the a duplicate natural id exists.
First, yes, you choose what the PK includes, so you can have it include both a natural and a surrogate key.
This part of your thought is strange to me: "auto-incrementing key that only increments if the a duplicate natural id exists". What leads you to ask this? You already admit the natural key alone cannot cover you. Why go to great lengths to avoid having a surrogate value if the natural one is not unique? Even then, you would just be able to use some obscure trigger to change the value to, for example, zero; it would still need to have a value, as part of the PK.
If what you have in mind is performance via the natural key, sure, just make the PK's first column the natural key and second one the surrogate, so you can benefit from the clustered index.