Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server insert if not exists best practice [closed]

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.

like image 481
Didier Levy Avatar asked Mar 13 '11 08:03

Didier Levy


People also ask

How do you insert unmatched records in SQL?

INSERT without "(Field1,Field2)" ? It is a valid syntax. As UnmatchedTable, is just a name to the temp table returned from the nested part. Running the query again and again will add records to Table3 since they are independent.

How do you insert values in SQL if not exists?

The basic syntax for INSERT IF NOT EXISTS is as follows. Copy INSERT INTO name_of_the_table (column_name) SELECT * FROM (SELECT value_name) AS val WHERE NOT EXISTS (<conditonal expression>); In the name_of_the_table we insert the value_name in the column_name if the conditional expression is met.

What to use instead of not exists in SQL?

Using Joins Instead of IN or EXISTS An alternative for IN and EXISTS is an INNER JOIN, while a LEFT OUTER JOIN with a WHERE clause checking for NULL values can be used as an alternative for NOT IN and NOT EXISTS.


4 Answers

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)
like image 59
gbn Avatar answered Sep 21 '22 16:09

gbn


Another option is to left join your Results table with your existing competitors Table and find the new competitors by filtering the distinct records that don´t match int the join:

INSERT Competitors (cName)
SELECT  DISTINCT cr.Name
FROM    CompResults cr left join
        Competitors c on cr.Name = c.cName
where   c.cName is null

New syntax MERGE also offer a compact, elegant and efficient way to do that:

MERGE INTO Competitors AS Target
USING (SELECT DISTINCT Name FROM CompResults) AS Source ON Target.Name = Source.Name
WHEN NOT MATCHED THEN
    INSERT (Name) VALUES (Source.Name);
like image 40
pcofre Avatar answered Sep 20 '22 16:09

pcofre


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 ON c.[ID] = cc.[CompetitionID]

And when you have a new competition with new competitors then you simply check which ones already exist in the Competitors table. If they already exist then you don't insert into Competitor for those Competitors and do insert for the new ones.

Then you insert the new Competition in Competition and finally you just make all the links in CompetitionCompetitors.

like image 24
Transact Charlie Avatar answered Sep 18 '22 16:09

Transact Charlie


You will need to join the tables together and get a list of unique competitors that don't already exist in Competitors.

This will insert unique records.

INSERT Competitors (cName) 
SELECT DISTINCT Name
FROM CompResults cr LEFT JOIN Competitors c ON cr.Name = c.cName
WHERE c.Name IS NULL

There may come a time when this insert needs to be done quickly without being able to wait for the selection of unique names. In that case, you could insert the unique names into a temporary table, and then use that temporary table to insert into your real table. This works well because all the processing happens at the time you are inserting into a temporary table, so it doesn't affect your real table. Then when you have all the processing finished, you do a quick insert into the real table. I might even wrap the last part, where you insert into the real table, inside a transaction.

like image 31
richard Avatar answered Sep 17 '22 16:09

richard