MS SQL Update AND Insert using MERGE Example

MS_SQL_Update_and_Insert.sql

-- Using MS SQL 2008 and above..

-- Create a test table first
CREATE TABLE dbo.DataType
(
    ID             int IDENTITY(1,1),
    TypeName       nvarchar(255),
    [TypeProperty] nvarchar(255),
    CONSTRAINT PK_DataType PRIMARY KEY (ID)
);

INSERT dbo.DataType(TypeName, [TypeProperty]) VALUES (N'name 1', N'property 1');
GO

-- The stored procedure
CREATE PROCEDURE dbo.MergeDataType
    @ID           int = NULL,
    @TypeName     nvarchar(255),
    @TypeProperty nvarchar(255)
AS
BEGIN
    SET NOCOUNT ON;

    WITH [source](ID, TypeName, [TypeProperty]) AS 
    (
        SELECT @ID, @TypeName, @TypeProperty
    )
    MERGE dbo.DataType WITH (HOLDLOCK) AS [target] 
      USING [source] ON [target].ID = [source].ID
    WHEN MATCHED THEN
        UPDATE SET [target].TypeName       = @TypeName,
                   [target].[TypeProperty] = @TypeProperty
    WHEN NOT MATCHED THEN
        INSERT (TypeName, [TypeProperty]) 
        VALUES (@TypeName, @TypeProperty);
END
GO

-- Tests
EXEC dbo.MergeDataType 
    @TypeName     = N'foo', 
    @TypeProperty = N'bar';

EXEC dbo.MergeDataType 
    @ID           = 1, 
    @TypeName     = N'name 1', 
    @TypeProperty = N'new property';
GO

SELECT ID, TypeName, [TypeProperty] FROM dbo.DataType;
GO