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?

Answer 1

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