SQL Server如果不存在则插入最佳做法

Question

我有一个Competitions结果表，其中包含团队成员的姓名，和他们的排名一方面。

另一方面，我需要维护一个包含唯一竞争者名称的表

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

现在，我在第一个表和中有大约200,000个结果，当表为空时，我可以执行以下操作：

INSERT INTO Competitors SELECT DISTINCT Name FROM CompResults

并且该查询只需要大约5秒钟来插入大约11,000个名字。

到目前为止，这还不是一个关键的应用程序，所以当我收到大约10,000行的新竞赛结果时，我可以考虑每个月一次截断竞争对手表。

但是，对于新的和现有的竞争对手，添加新的结果时，最佳实践是什么？我不想截断现有的竞争对手表

我只需要为新的竞争者执行INSERT语句，如果他们存在，则不执行任何操作。

Answer 1

从语义上讲，你是在问“在尚不存在的地方插入竞争对手”：

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

Answer 2

另一种选择是左连接您的结果表和现有的竞争对手表，并通过过滤连接中不匹配的不同记录来查找新的竞争对手：

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

新的语法MERGE还提供了一种紧凑、优雅和高效的方式来实现这一点：

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);

Answer 3

不知道为什么其他人还没有这么说；

正常化。

你有一张模拟比赛的桌子？比赛是由竞争者组成的吗？您需要在一个或多个比赛中列出不同的参赛者名单......

你应该有下面的表格.....

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]
        )
    )

CompetitionCompetitors.CompetitionID和CompetitorID上的约束指向其他表。

对于这种表结构--您的键都是简单的整数--似乎没有一个很好的自然键适合这个模型，所以我认为代理键很适合这里。

因此，如果您有这个，那么要获得特定竞争对手的不同列表，您可以发出如下查询：

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
        )

如果你想要竞争对手参加的每一场比赛的分数：

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]

当您与新的竞争对手有新的竞争时，您只需检查竞争对手表中已经存在的竞争对手。如果它们已经存在，那么对于那些竞争对手，您就不需要插入到竞争对手中，而对于新的竞争对手，则需要插入。

然后在竞争中插入新的竞争，最后在CompetitionCompetitors中创建所有链接。