SQL Server插入(如果不存在)最佳实践 [英] SQL Server insert if not exists best practice

问题描述

我有一个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.

到目前为止，这并不是一个至关重要的应用程序，因此当我收到包含约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插入(如果不存在)最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！