CODEWITHSUNDEEP
sqlsql-server-2008
barsan
barsan

Reputation: 2491

How to avoid Duplicate values for INSERT in SQL?

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?

Upvotes: 11

Views: 67142

Answers (6)

mattcornish
mattcornish

Reputation: 31

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

Upvotes: 3

Hari Das
Hari Das

Reputation: 10914

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
)

Upvotes: 2

Doug S
Doug S

Reputation: 10315

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.

Upvotes: 0

walsh06
walsh06

Reputation: 97

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

Upvotes: 1

gzaxx
gzaxx

Reputation: 17600

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)

Upvotes: 7

gbn
gbn

Reputation: 432667

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

Upvotes: 20

Related Questions