implementing-merge-functionality-in-a-natively-compiled-stored-procedure.md

title	Implementing MERGE Functionality | Microsoft Docs
ms.custom
ms.date	06/13/2017
ms.prod	sql-server-2014
ms.reviewer
ms.technology	in-memory-oltp
ms.topic	conceptual
ms.assetid	d4bcdc36-3302-4abc-9b35-64ec2b920986
author	MightyPen
ms.author	genemi
manager	craigg

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 [!INCLUDEtsql]:

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

Another [!INCLUDEtsql] 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....  

See Also

Migration Issues for Natively Compiled Stored Procedures
Transact-SQL Constructs Not Supported by In-Memory OLTP