SQL Server 插入如果不存在最佳实践

问题描述

我有一个 Competitions 结果表，一方面保存了团队成员的姓名和他们的排名.

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)

现在我在第一个表中有大约 200,000 个结果，当竞争对手表为空时我可以执行此操作:

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

查询只需要 5 秒钟就可以插入大约 11,000 个名字.

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

到目前为止，这不是一个关键的应用程序，因此我可以考虑每月一次截断 Competitors 表，当我收到大约 10,000 行的新比赛结果时.

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

我只需要对新的竞争者执行 INSERT 语句，如果存在则什么都不做.

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)

这篇关于SQL Server 插入如果不存在最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！