Trigger update if duplicate record

Transact-SQL
1

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

2

Yes. use Unique index
https://msdn.microsoft.com/en-us/library/ms187019.aspx

or unique constraint
https://msdn.microsoft.com/en-us/library/ms190024.aspx

3

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

INSERT INTO TABLE (ID, COLA, COLB, COLC)
VALUES
(1, 'TESTA', 'TESTB', 'TESTC')

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

4

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

5

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

6

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

but

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

try googling it.

7

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