Fast upsert Sql server 2008 R2

Question

I'm trying to upsert records using SP into one table.

CREATE TABLE [dbo].[SHARE_AD_GROUP](
    [SHARE_AD_GROUP_ID] [int] IDENTITY(1,1) NOT NULL,
    [SHARE_ID] [int] NOT NULL,
    [AD_GROUP] [varchar](200) NOT NULL,
    [SHARE_PERMISSIONS] [varchar](65) NULL,

what is the best way of the following:

1-

ALTER PROCEDURE [dbo].[PROC_INSERT_SHARE_AD_GROUP]


@shareID int,
@ownerId varchar(200),
@sharePermissions varchar(65)

AS

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    UPDATE [dbo].SHARE_AD_GROUP
               SET 
                   [SHARE_PERMISSIONS] = @sharePermissions
             WHERE SHARE_ID = @shareID and [AD_GROUP] = @ownerId
    if @@ROWCOUNT =0 
    begin
    INSERT INTO [dbo].SHARE_AD_GROUP
                       (SHARE_ID,[AD_GROUP],[SHARE_PERMISSIONS])
                 VALUES
                       (@shareID,@ownerId,@sharePermissions)
    end

end

2-

   BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

    declare @id int
    SET NOCOUNT ON;
    set @id = (select top 1 SHARE_AD_GROUP_ID from SHARE_AD_GROUP where SHARE_ID = @shareID and [AD_GROUP] = @ownerId)
        if @id is null
            begin
                INSERT INTO [dbo].SHARE_AD_GROUP
                       (SHARE_ID,[AD_GROUP],[SHARE_PERMISSIONS])
                 VALUES
                       (@shareID,@ownerId,@sharePermissions)
                 set @id = SCOPE_IDENTITY()
            end
        else
            begin
            UPDATE [dbo].SHARE_AD_GROUP
               SET 
                   [SHARE_PERMISSIONS] = @sharePermissions
             WHERE SHARE_ID = @shareID and [AD_GROUP] = @ownerId
            end
End

3- Merge ==> No idea how to write that.

What is the fastest way in your opinion?

Answer 1

I think merge statement is faster then what you demonstrate in your 2 ways. if you need to know more about how to write MERGE INTO Query in sql server then please follow the links..

http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/

https://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/

and your merge block looks like this, you need to put that in your store procedure or any other places.

MERGE INTO SHARE_AD_GROUP A
USING (
    SELECT SHARE_AD_GROUP_ID,
        SHARE_ID,
        AD_GROUP,
        SHARE_PERMISSIONS
    FROM SHARE_AD_GROUP
    WHERE SHARE_ID = @shareID AND AD_GROUP = @ownerId
) B ON (A.SHARE_AD_GROUP_ID = B.SHARE_AD_GROUP_ID)
WHEN MATCHED THEN
    UPDATE SET A.SHARE_PERMISSIONS = B.SHARE_PERMISSIONS
WHEN NOT MATCHED THEN
    INSERT (SHARE_PERMISSIONS) VALUES(@sharePermissions);