How to avoid Duplicate values for INSERT in SQL?

Question

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?

What is the natural unique key then? Not the IDENTITY value – gbn Jul 03 '13 at 08:14 — gbn, Jul 03 '13 at 08:14
no unique key is there actually. – barsan Jul 03 '13 at 08:15 — barsan, Jul 03 '13 at 08:15
@barsan: well, can't be done then. – gbn Jul 03 '13 at 08:16 — gbn, Jul 03 '13 at 08:16

score 20 · Answer 1 · answered Jul 03 '13 at 08:15

20

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

answered Jul 03 '13 at 08:15

gbn

422,506
82
585
676

I think you have an extra close parentheses on Insert line? – G-Force Mar 09 '22 at 23:44

score 7 · Accepted Answer · answered Jul 03 '13 at 08:16

7

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)

answered Jul 03 '13 at 08:16

gzaxx

17,312
2
36
54

10

This will fail under high load – gbn Jul 03 '13 at 08:17
Didn't know that, putting it inside of transaction would fix that? – gzaxx Jul 03 '13 at 08:18
1

@gzaxx: no it won't unless you use a performance killing lock strategy – gbn Jul 03 '13 at 08:25
1

I have been using this approach for years. However now i'm experiencing duplicate inserts several times a day. So I can confirm that this fails under high load. – Joakim May 08 '22 at 19:28

score 3 · Answer 3 · answered Feb 21 '18 at 15:39

3

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

answered Feb 21 '18 at 15:39

mattcornish

31
1

score 2 · Answer 4 · edited Jun 02 '16 at 09:47

2

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
)

edited Jun 02 '16 at 09:47

Alex Weitz

3,199
4
34
57

answered Jul 03 '13 at 08:19

Hari Das

10,145
7
62
59

1

I get an error when using the WHERE clause with INSERT INTO... VALUES. The consensus seems to be that you must use an INSERT SELECT. – atjoedonahue Dec 12 '19 at 19:11

score 1 · Answer 5 · answered Jul 03 '13 at 08:16

1

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

answered Jul 03 '13 at 08:16

walsh06

97
1
9

score 0 · Answer 6 · answered Oct 05 '15 at 03:26

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)

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.

How to avoid Duplicate values for INSERT in SQL?

6 Answers6

Linked

Related