HELP_ME HELP_ME - 10 days ago 7
SQL Question

simple insert procedure, check for duplicate

I am creating a program that is going to insert data into a table which is pretty simple

But my issue is I want my insert statement to make sure that it isnt inserting duplicate data

I want to somehow check the table the data is going into to make sure that there isnt a row with the same indivualid and categoryid and value

So if I am inserting

indivualid = 1
categorid = 1
value = 1


and in my table there is a row with

indivualid = 1
categorid = 1
value = 2


my data would still be inserted

but if there was a row with

indivualid = 1
categorid = 1
value = 1


then it wouldnt

I tried this

IF @value = 'Y'
OR @value = 'A'
OR @value = 'P'
AND NOT EXISTS
(SELECT categoryid,
individualid
FROM ualhistory
WHERE categoryid = @cat
AND individualid = @id)
INSERT INTO individuory(categoryid, individualid, value, ts)
VALUES (@cat,
@id,
@yesorno,
getdate())


but it still inserts duplicates.

Answer

You can do that in the following manner:

insert into 
individuory(categoryid, individualid, value, ts) 
VALUES (@cat, @id, @yesorno, getdate()) 
where not exists 
(select 1 from individuory where categoryid=@cat and individualid=@id)

Now, the exact problem with your approach is that you are not associating the ORs and therefore, the condition becomes true and always inserts the data. You can change your statement to this:

if ((@value = 'Y' or @value = 'A' or @value = 'P') 
and not EXISTS 
(SELECT categoryid, individualid FROM ualhistory WHERE categoryid = @cat 
 and individualid = @id) )
 INSERT INTO individuory(categoryid, individualid, value, ts) 
 VALUES (@cat, @id, @yesorno, getdate()) 

And I think it will work also.