如何避免SQL中INSERT的重复值？

I have one table named:

我有一个名为的表:

Delegates

This table has four fields:

该表有四个字段:

ID(Auto increment, Primary)
MemberNo, FromYr, ToYr

I am inserting with this query:

我正在使用此查询插入:

INSERT INTO Delegates ([MemNo],[FromYr],[ToYr]) values(@MemNo, @FromYr,@ToYr)

The values comes from user input. One member can be a Delegate for any year that's why I allow them to input as they want. But now problem is they can insert mistakenly one member for the same year more than 2 times. Please help me what can I do now here?

值来自用户输入。一个成员可以是任何一年的代表,这就是我允许他们按照自己的意愿输入的原因。但现在问题是他们可以错误地将同一个成员插入同一年超过2次。请帮帮我,我现在能做什么?

6 个解决方案

#1

Before inserting check if there is a record with the same values:

在插入之前检查是否存在具有相同值的记录:

if not exists (select * from Delegates d where d.FromYr = @FromYr and d.MemNo = @MemNo)
    INSERT INTO Delegates ([MemNo],[FromYr],[ToYr]) values(@MemNo, @FromYr,@ToYr)

#2

Use MERGE

MERGE INTO Delegates D
USING (values(@MemNo, @FromYr,@ToYr)) X ([MemNo],[FromYr],[ToYr])
ON (insert unique key join)
WHEN NOT MATCHED BY TARGET THEN
INSERT ([MemNo],[FromYr],[ToYr]))
VALUES (X.[MemNo],X.[FromYr],X.[ToYr]);

#3

Try this, (I have not verified)

试试这个,(我还没有验证)

INSERT INTO Delegates ([MemNo],[FromYr],[ToYr]) values(@MemNo, @FromYr,@ToYr)
where @MemNo not in 
(
    SELECT MemNo FROM words WHERE FromYr = @FromYr
)

#4

Just add a unique index on that column, then inserting duplicates will cause an error. You can then error handle it if it needs to fail gracefully

只需在该列上添加唯一索引,然后插入重复项就会导致错误。如果需要优雅地失败,则可以对其进行错误处理

#5

make a stored procedure that will first make a check on the whether the values are already contained in the DB. if they arent you will do your insert. If they simply ignore it

制作一个存储过程,首先检查数据库中是否已包含这些值。如果他们不是你会做你的插入。如果他们完全忽略它

#6

You can avoid inserting duplicates with this simple, one line of code:

您可以使用这一简单的一行代码避免插入重复项:

INSERT INTO Delegates (MemNo, FromYr, ToYr) SELECT @MemNo, @FromYr, @ToYr WHERE NOT EXISTS (SELECT 1 FROM Delegates d WHERE d.MemNo=@MemNo AND d.FromYr=@FromYr)

INSERT INTO代表(MemNo,FromYr,ToYr)SELECT @MemNo,@ FryYr,@ ToYr WHERE NOT EXISTS(SELECT 1 FROM Delegates d WHERE d.MemNo=@MemNo AND d.FromYr=@FromYr)

If it's a high load environment where another command could insert the duplicate while this command is executing, you can use the WITH(HOLDLOCK) hint.

如果它是一个高负载环境,其中另一个命令可以在执行此命令时插入副本,则可以使用WITH(HOLDLOCK)提示。

#1