MS SQL SERVER prevent duplicates being inserted
Posted on 2015-02-10
I have a table that contains 3 fields as clustered index that is used to prevent duplicates to a temporary table used to grab data from an outside source.
The next step is copying this data to an active table that uses the fields from the clustered index for most of the rows of data, but not all so I can't duplicate the clustered index on the live table.
I have scripts that auto populate the temp table several times a day as well as copying the data over.
TEMP_TABLE Clustered index on key1,key2 and key3 Ignore Duplicate Values=true
What is the best option to copy the data from the temp to live and avoid duplicates on key1,key2 and key3 where there will be rows of data that cause the key fields to be null as they will not be needed.
When the key fields are not needed, data is entered as a one off. When the key fields are used it will be brought in via an sql script.