Export (0) Print
Expand All

Implementing MERGE Functionality

 

A database may need to perform either an insert of an update, depending on whether a particular row already exists in the database.

Without using the MERGE statement, the following is one approach you can use in Transact-SQL:

UPDATE mytable SET col=@somevalue WHERE myPK = @parm
IF @@ROWCOUNT = 0
    INSERT mytable (columns) VALUES (@parm, @other values)

Another Transact-SQL method to implement a merge:

IF EXISTS (SELECT 1 FROM mytable WHERE myPK = @parm)
    UPDATE….
ELSE
    INSERT

For a natively compiled stored procedure

DECLARE @i  int  = 0  -- or whatever your PK data type is
UPDATE mytable SET @i=myPK, othercolums = other values WHERE myPK = @parm
IF @i = 0
   INSERT….
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft