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