SQL Server insert if not exists best practice

168

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.

sql-server insert-update

Edit Report

Don't know why anyone else hasn't said this yet;

NORMALISE.

You've got a table that models competitions? Competitions are made up of Competitors? You need a distinct list of Competitors in one or more Competitions......

You should have the following tables.....

CREATE TABLE Competitor (
    [CompetitorID] INT IDENTITY(1,1) PRIMARY KEY
    , [CompetitorName] NVARCHAR(255)
    )

CREATE TABLE Competition (
    [CompetitionID] INT IDENTITY(1,1) PRIMARY KEY
    , [CompetitionName] NVARCHAR(255)
    )

CREATE TABLE CompetitionCompetitors (
    [CompetitionID] INT
    , [CompetitorID] INT
    , [Score] INT

    , PRIMARY KEY (
        [CompetitionID]
        , [CompetitorID]
        )
    )

With Constraints on CompetitionCompetitors.CompetitionID and CompetitorID pointing at the other tables.

With this kind of table structure -- your keys are all simple INTS -- there doesn't seem to be a good NATURAL KEY that would fit the model so I think a SURROGATE KEY is a good fit here.

So if you had this then to get the the distinct list of competitors in a particular competition you can issue a query like this:

DECLARE @CompetitionName VARCHAR(50) SET @CompetitionName = 'London Marathon'

    SELECT
        p.[CompetitorName] AS [CompetitorName]
    FROM
        Competitor AS p
    WHERE
        EXISTS (
            SELECT 1
            FROM
                CompetitionCompetitor AS cc
                JOIN Competition AS c ON c.[ID] = cc.[CompetitionID]
            WHERE
                cc.[CompetitorID] = p.[CompetitorID]
                AND cc.[CompetitionName] = @CompetitionNAme
        )

And if you wanted the score for each competition a competitor is in:

SELECT
    p.[CompetitorName]
    , c.[CompetitionName]
    , cc.[Score]
FROM
    Competitor AS p
    JOIN CompetitionCompetitor AS cc ON cc.[CompetitorID] = p.[CompetitorID]
    JOIN Competition AS c O


                        
                        
                            answered 2011-03-13T10:06:14.633

SQL Server insert if not exists best practice

1 Answer

Your Answer