Posted By: Anonymous
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.
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)