Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

From time to time my stored procedures looks like

create procedure handle_data 
         @fk int 
        ,@value varchar(10)
            if exists (select * from my_table where id=@fk)
                update my_table
                    set value=@value
                where id=@fk
                        insert into my_table
                        select @fk,@value

probably there is fault in design of app which calls this sproc.
Should I avoid making apps which do same sproc and methods for inserting new data and also updating old ones?

Is there better way to achieve updating or inserting data in one approach ?
I am using MSSQL2005/8.

share|improve this question
up vote 5 down vote accepted

A slightly more efficient way (which will do at worst one seek/scan instead of two against the existing data):

UPDATE dbo.whatever SET ... WHERE key = @key;
  INSERT dbo.whatever ...

MERGE may be tempting, however there are a few reasons I shy away from it.

  1. the syntax is daunting and hard to memorize
  2. you don't get any more concurrency than the above approach unless you intentionally add specific locking hints
  3. there are many unresolved bugs with MERGE and probably many more that have yet to be uncovered
share|improve this answer
IF @@ROWCOUNT = 0 would probably be better for the insert, just in case you do something that requires two rows be inserted, a stored procedure that doesn't process the update based on the primary key for example. – mrdenny Apr 10 '13 at 22:52
@mrdenny I based that on where id= in the question - which certainly implies an identifier (or is very poorly named). – Aaron Bertrand Apr 10 '13 at 22:54
@AaronBertrand Then I should avoid adding SET NOCOUNT ON; inside my sprc ? – adopilot Apr 11 '13 at 7:44
In this case I agree either would work. However if this is used as a template for other procedures that may not get changed if needed. – mrdenny Apr 11 '13 at 7:57
@adopilot no, your stored procedure should still have SET NOCOUNT ON. This prevents chatty "n row(s) affected" messages from being sent to the client, but does not interfere with your ability to use @@ROWCOUNT. – Aaron Bertrand Apr 11 '13 at 15:13

MERGE statement will let you do it one command,

Create procedure handle_data 
  @fk int 
  , @value varchar(10)

MERGE my_table AS target
USING (SELECT @fk, @value) AS source (fk, value)
ON ( =
    UPDATE SET value = source.Value
    INSERT (id, value)
    VALUES (, source.value);


More information about merge can be found at this article on MSDN

share|improve this answer

The MERGE statement was introduced in SQL Server 2008 to handle exactly this situation.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.