Upsert Over Exists In Stored Procedures
It is a common requirement in database transactions to have a procedure that inserts a record to a table in a database if the record does not exist or, if the record already exists, updates the existing record. Thus far, I have been doing the (if exists then) method as follows…
IF EXISTS( SELECT 1 FROM MY_TABLE WHERE col1=@col1 AND col2=@col2) --Update Statement UPDATE myTable SET Col1=@col1, Col2=@col2 WHERE ID=@ID ELSE --Insert Statement INSERT INTO myTable (Col1, Col2) VALUES (@col1, @col2)
I have discovered that there is another (more efficient) method of performing this procedure known as upsert or merge which has a cheaper I/O requirement.
--Update Statement UPDATE myTable set Col1=@col1, Col2=@col2 WHERE ID=@ID if @@rowcount = 0 --Insert Statement INSERT INTO myTable (Col1, Col2) VALUES (@col1, @col2)
A good discussion on the merits of performing the upsert in this way can be found at SqlServerCentral.com.