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

查看:33
本文介绍了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 插入如果不存在最佳实践的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆