SQL SERVER: Generates (DUI or UPSERT) delete, update, insert statements instead of a MERGE statement to merge data into a table.

dbo.sp_generate_merge_crud.sql

USE master

IF OBJECT_ID (N'dbo.sp_generate_merge_crud') IS NULL BEGIN
    EXEC('CREATE PROCEDURE dbo.sp_generate_merge_crud AS SELECT ''PROCEDURE STUB, TO BE REPLACED'';'); 
END
GO 

/*
    TIM C: Because of issues with the MERGE statement (https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/) 
    I decided to write a generator that will generate a pseduo merge using regular crud statements.

    Parameters: 
        - @table: The table name, should include schema
        - @generate_delete: IF 1 then the delete query is generated, else the delete query is omitted
*/
ALTER PROCEDURE dbo.sp_generate_merge_crud (
    @table sysname, 
    @generate_delete BIT = 1
)
AS 
BEGIN
    BEGIN --declares
    DECLARE 
        @crlf CHAR(2) = CONCAT(CHAR(13), CHAR(10)),
        @tab CHAR(1) = CHAR(9),
        @table_name sysname,
        @has_identity BIT = 0,
        @set_identity_insert VARCHAR(500)

    DECLARE @table_columns TABLE (
        [schema_name] sysname COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [table_name] sysname COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [column_name] sysname COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [data_type] VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [is_identity] BIT NOT NULL,
        [is_pk] BIT NOT NULL,
        [column_id] INT NOT NULL
    )
    END
    
    BEGIN -- error checking
    DECLARE @msg VARCHAR(1000) 
    IF OBJECT_ID(@table) IS NULL BEGIN 
        SET @msg = CONCAT('The table (', @table, ') does not exist.', @crlf, @crlf)
    END
    IF NOT EXISTS (SELECT 1 FROM sys.indexes i WHERE i.object_id = OBJECT_ID(@table) AND i.is_primary_key = 1) BEGIN
        SET @msg = CONCAT(@msg, 'The table (', @table, ') specified does not have a primary key.')
    END
    END

    IF LEN(@msg) > 0 BEGIN
        ;THROW 60000, @msg, 1;
    END

    BEGIN -- metadata setup
    INSERT INTO @table_columns (schema_name, table_name, column_name, [data_type], is_identity, is_pk, column_id)
    SELECT OBJECT_SCHEMA_NAME(c.object_id) COLLATE SQL_Latin1_General_CP1_CI_AS AS [schema_name],
        OBJECT_NAME(c.object_id)  COLLATE SQL_Latin1_General_CP1_CI_AS AS [table_name], 
        c.name  COLLATE SQL_Latin1_General_CP1_CI_AS AS [column_name], 
        CONCAT(t.name, 
            CASE 
                WHEN t.name LIKE '%char' OR t.name LIKE '%binary'
                    THEN CONCAT(' (',
                        CASE 
                            WHEN c.max_length=-1 THEN 'MAX'
                            ELSE 
                                CAST(CASE 
                                    WHEN t.name IN ('nchar','nvarchar') THEN  c.max_length/2 
                                    ELSE c.max_length 
                                END AS VARCHAR(12)) 
                          END, ')'
                    )
                WHEN t.name IN ('decimal','numeric')
                    THEN CONCAT('(', c.precision, ',', c.Scale, ')')
                WHEN t.name = 'datetime2'
                    THEN CONCAT('(', c.scale, ')')
                ELSE '' 
            END, CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END, ' DEFAULT' + d.definition) COLLATE SQL_Latin1_General_CP1_CI_AS AS data_type , 
        c.[is_identity],
        CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS [is_pk],
        c.[column_id]
    FROM sys.columns c 
    INNER JOIN sys.types t 
        ON  t.user_type_id = c.user_type_id 
    LEFT JOIN sys.default_constraints AS d  
        ON d.parent_object_id = c.object_id
        AND d.parent_column_id = c.column_id  
    LEFT JOIN (
        SELECT ic.object_id, ic.column_id  
        FROM sys.indexes i 
        INNER JOIN sys.index_columns ic 
            ON  ic.object_id = i.object_id 
            AND ic.index_id = i.index_id
        WHERE i.object_id = OBJECT_ID(@table) 
            AND i.is_primary_key = 1
    ) ic 
        ON ic.object_id = c.object_id 
        AND ic.column_id = c.column_id
    WHERE c.object_id = OBJECT_ID(@table) 
        AND OBJECTPROPERTY(c.object_id, 'IsMSShipped') = 0
    ORDER BY c.column_id
    
    SELECT TOP (1) @table_name = CONCAT(QUOTENAME(tc.schema_name), '.', QUOTENAME(tc.table_name))
    FROM @table_columns tc

    SELECT @has_identity = 1
    FROM @table_columns tc
    WHERE tc.is_identity = 1

    IF @has_identity = 1 BEGIN
        SET @set_identity_insert = '#CRLF##TAB#SET IDENTITY_INSERT {table_name} {switch};'
    END

    DECLARE @on_clause VARCHAR(8000) = STUFF((
        SELECT CONCAT('#CRLF##TAB##TAB#AND [target].', QUOTENAME(tc.column_name), ' = ', '[source].', QUOTENAME(tc.column_name)) 
        FROM @table_columns tc 
        WHERE tc.is_pk = 1
        FOR XML PATH('')
    ), 1, 20, '')

    DECLARE @create_table VARCHAR(MAX) = CONCAT('
    CREATE TABLE #source (
          ', STUFF((
            SELECT CONCAT('#CRLF##TAB##TAB#, ', QUOTENAME(tc.column_name), ' ', UPPER(tc.data_type), CASE WHEN tc.is_identity = 1 THEN ' IDENTITY' ELSE '' END) 
            FROM @table_columns tc
            FOR XML PATH('')
        ), 1, 18, ''), '
        , PRIMARY KEY ', (SELECT i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS FROM sys.indexes i WHERE i.object_id = OBJECT_ID(@table) AND i.is_primary_key = 1),' (', 
            STUFF((
            SELECT CONCAT(', ', QUOTENAME(tc.column_name)) 
            FROM @table_columns tc
            WHERE tc.is_pk = 1 
            FOR XML PATH('')
        ), 1, 2, ''), ')
    );#CRLF#')
	
    DECLARE @insert_table VARCHAR(MAX) = CONCAT('
    ', REPLACE(REPLACE(@set_identity_insert, '{table_name}', '#source'), '{switch}', 'ON'), '
    INSERT INTO #source (
          ', STUFF((
            SELECT CONCAT('#CRLF##TAB##TAB#, ', QUOTENAME(tc.column_name)) 
            FROM @table_columns tc
            FOR XML PATH('')
        ), 1, 18, ''), '
    )
    SELECT [TODO: ADD SELECT VALUES HERE];
    
    ', REPLACE(REPLACE(@set_identity_insert, '{table_name}', '#source'), '{switch}', 'OFF'), '#CRLF#')
    END

    BEGIN -- merge queries
    DECLARE @delete_query VARCHAR(MAX) = '' 
    
    IF @generate_delete = 1 BEGIN
        SET @delete_query = CONCAT('
    RAISERROR (''Deleting rows from ', @table_name, ' where the rows do not match the rows in the #source table'', 0, 1) WITH NOWAIT;
    DELETE [target]
    FROM ', @table_name, ' AS [target]
    LEFT JOIN #source AS [source] 
        ON  ', @on_clause, '
    WHERE   ', STUFF((
        SELECT CONCAT('#CRLF##TAB##TAB#AND [source].', QUOTENAME(tc.column_name), ' IS NULL') 
        FROM @table_columns tc 
        WHERE tc.is_pk = 1
        FOR XML PATH('')
    ), 1, 20, ''), ';
    ')
    END

    DECLARE @update_query VARCHAR(MAX) =  CONCAT('
    RAISERROR (''Updating rows in ', @table_name, ' that match rows in #source and have differences'', 0, 1) WITH NOWAIT;
    UPDATE [target]
        SET   ', STUFF((
        SELECT CONCAT('#CRLF##TAB##TAB##TAB#, [target].', QUOTENAME(tc.column_name), ' = [source].', QUOTENAME(tc.column_name)) 
        FROM @table_columns tc
        WHERE tc.is_pk = 0 
	        AND tc.is_identity = 0
        FOR XML PATH('')
    ), 1, 23, ''), '
    FROM ', @table_name, ' AS [target]
    INNER JOIN #source AS [source]
        ON  ', @on_clause, '
    WHERE   ', STUFF((
        SELECT CONCAT('#CRLF##TAB##TAB#AND [target].', QUOTENAME(tc.column_name), ' <> [source].', QUOTENAME(tc.column_name)) 
        FROM @table_columns tc
        WHERE tc.is_pk = 0
        FOR XML PATH('')
    ), 1, 20, ''), ';
    ')

    DECLARE @insert_query VARCHAR(MAX) =  CONCAT('
    RAISERROR (''Inserting rows into ', @table_name, ' from #source that are not in ', @table_name, ''', 0, 1) WITH NOWAIT;', 
    REPLACE(REPLACE(@set_identity_insert, '{table_name}', @table_name), '{switch}', 'ON'), '
    INSERT INTO ', @table_name, ' (#CRLF##TAB##TAB#  ', STUFF((
        SELECT CONCAT('#CRLF##TAB##TAB#, ', QUOTENAME(tc.column_name)) 
        FROM @table_columns tc
        FOR XML PATH('')
    ), 1, 18, ''), '
    )
    SELECT ', STUFF((
        SELECT CONCAT('#CRLF##TAB##TAB#, [source].', QUOTENAME(tc.column_name)) 
        FROM @table_columns tc
        FOR XML PATH('')
    ), 1, 18, ''), '
    FROM ', @table_name, ' AS [target]
    RIGHT JOIN #source AS [source] 
        ON  ', @on_clause, '
    WHERE   ', STUFF((
        SELECT CONCAT('#CRLF##TAB##TAB#AND [target].', QUOTENAME(tc.column_name), ' IS NULL') 
        FROM @table_columns tc 
        WHERE tc.is_pk = 1
        FOR XML PATH('')
    ), 1, 20, ''), ';',
    REPLACE(REPLACE(@set_identity_insert, '{table_name}', @table_name), '{switch}', 'OFF'), '
    ')
    END

    BEGIN -- select output

    SELECT 'Please ensure the option "Query Results -> Results to Grid -> Retain CR/LF on copy or save" is enabled for these query results.' AS [READ ME FIRST!],
        REPLACE(REPLACE(REPLACE(REPLACE(
            CONCAT(
                @create_table, 
                @insert_table, 
                @delete_query, 
                @update_query, 
                @insert_query
            ), '#CRLF#', @crlf), '#TAB#', @tab), '&lt;', '<'), '&gt;', '>') AS [complete_query]
        
    END
    --SELECT * FROM @table_columns

END
GO

EXEC sys.sp_MS_marksystemobject @objname = N'dbo.sp_generate_merge_crud'
GO
GRANT EXECUTE ON dbo.sp_generate_merge_crud TO PUBLIC
GO