Trigger update if duplicate record

Hi i am looking for the best way to do an Insert/Update within a Stored Procedure for a lot of records, a cursor would be way to slow, so i was hoping i could do a "Insert - Select From" and then have a trigger to rollback the record if it already existed and then update just after the rollback

is there a better way of doing this? i am using SQL 2014

Yes. use Unique index

or unique constraint

Hi Thanks for the reply, that will stop the duplicates which i understand, what I need to do though is if an insert has a duplicate ID then instead of inserting which would fail, it then updates the other columns.

Example - ID 1 already exists

(1, 'TESTA', 'TESTB', 'TESTC')

i need some kind of trigger to either stop the insert or rollback and then update COLA, COLB, COLC

then you will need to use INSTEAD OF INSERT trigger to do that

why don't you handle this in your query ? You can use MERGE to do that

ok merge could work, which is faster merge or adding a WHERE NOT IN to the INSERT - SELECT FROM?

i can't be certain but i have read somewhere there it depends on the actual operation.

for record not exists, INSERT .. WHERE NOT EXISTS is faster than MERGE


for record existed, using MERGE is faster than UPDATE . . INSERT

try googling it.

merge is working great, thanks for the quick response and help.