SQL Server insert if not exists best practice

Question

I have a Competitions results table which holds team member's names and their ranking on one hand.

On the other hand I need to maintain a table of unique competitors names:

CREATE TABLE Competitors (cName nvarchar(64) primary key)

Now I have some 200,000 results in the 1st table and when the competitors table is empty I can perform this:

INSERT INTO Competitors SELECT DISTINCT Name FROM CompResults

And the query only takes some 5 seconds to insert about 11,000 names.

So far this is not a critical application so I can consider truncate the Competitors table once a month, when I receive the new competition results with some 10,000 rows.

But what is the best practice when new results are added, with new AND existing competitors? I don't want to truncate existing competitors table

I need to perform INSERT statement for new competitors only and do nothing if they exists.

Answer 1

Semantically you are asking "insert Competitors where doesn't already exist":

INSERT Competitors (cName)
SELECT DISTINCT Name
FROM CompResults cr
WHERE
   NOT EXISTS (SELECT * FROM Competitors c
              WHERE cr.Name = c.cName)