Merge JSON array into SQL table

insert_json_array.sql

/*
CREATE TABLE [dbo].[test_jsonmerge](
	[id] [int] NOT NULL,
	[people_id] [varchar](50) NOT NULL,
	[last_name] [varchar](50) NULL,
	[sms_status] [varchar](50) NULL,
 CONSTRAINT [PK_test_jsonmerge] PRIMARY KEY CLUSTERED 
(
	[id] ASC,
	[people_id] ASC
)
)
DECLARE  
@pJson nvarchar(max) = N'[{"id":"1","people_id":"7777"},{"id":"2","people_id":"55555","last_name":"test"}]',
@pTableName sysname = 'dbo.test_jsonmerge'
EXEC dbo.merge_jsonarray @pJson, @pTableName 
*/
CREATE PROCEDURE dbo.merge_jsonarray
    @pJson nvarchar(max),
    @pTableName sysname
AS
BEGIN
    IF (ISJSON(@pJson) > 0)
    BEGIN
        IF (OBJECT_ID(@pTableName, N'U') IS NOT NULL) 
        BEGIN
            DECLARE @invalid_columns INT
            ;WITH
                CTE_count
                AS
                (
                    SELECT k2.[key] COLLATE SQL_Latin1_General_CP1_CI_AS AS [key]
                    FROM OPENJSON( @pJson) k1
                    CROSS APPLY OPENJSON(k1.value) k2
                    EXCEPT
                        SELECT name
                        FROM sys.columns
                        WHERE object_id = OBJECT_ID(@pTableName)
                )
            SELECT @invalid_columns = COUNT(*)
            FROM CTE_count

            IF (@invalid_columns = 0)
            BEGIN

                DECLARE 
                 @json_columns NVARCHAR(MAX)
                ,@insert_columns  NVARCHAR(MAX)
                ,@update_columns  NVARCHAR(MAX)
                ,@primary_key     NVARCHAR(MAX)
                ,@missing_pk NVARCHAR(100)
                ;WITH
                    matching_columns
                    AS
                    (
                        SELECT k2.[key] COLLATE SQL_Latin1_General_CP1_CI_AS AS [key]
                        FROM OPENJSON( @pJson) k1
                        CROSS APPLY OPENJSON(k1.value) k2
                        INTERSECT
                            SELECT name
                            FROM sys.columns
                            WHERE object_id = OBJECT_ID(@pTableName)
                    )
                SELECT
                    @json_columns = STRING_AGG(QUOTENAME(c.name) + ' ' + COALESCE(REPLACE(ty.name, 'uniqueidentifier', 'varchar'), 'varchar')
                        + IIF(COALESCE(REPLACE(ty.name, 'uniqueidentifier', 'varchar'), 'varchar') LIKE '%char%', '(' + CAST(ty.max_length AS VARCHAR(10)) + ')', ''), ',')
                    , @insert_columns = STRING_AGG(QUOTENAME(c.name), ',')
                    , @update_columns = STRING_AGG(QUOTENAME(c.name) + ' = s.' + QUOTENAME(c.name), ',')
                FROM sys.columns c
                    JOIN sys.types ty ON c.system_type_id = ty.system_type_id
                    JOIN matching_columns mc ON mc.[key] = c.name
                WHERE c.object_id = OBJECT_ID(@pTableName)

                ;WITH CTE_pk
                AS
                (
                    SELECT kc.name
                    FROM sys.key_constraints k
                    INNER JOIN sys.tables t ON k.parent_object_id = t.object_id
                    INNER JOIN sys.index_columns ic ON ic.object_id = t.object_id AND ic.index_id = k.unique_index_id
                    INNER JOIN sys.columns kc ON kc.object_id = t.object_id AND kc.column_id = ic.column_id
                    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
                    WHERE k.type = 'PK' AND  t.object_id = OBJECT_ID(@pTableName)
                )
                SELECT @primary_key = STUFF(
                    (SELECT ' AND ' + 't.' + QUOTENAME(kc.name) + ' = s.' + QUOTENAME(kc.name)
                    FROM CTE_pk kc
                    FOR XML PATH('')), 1, 5, '')

                ;WITH CTE_pk
                AS
                (
                    SELECT kc.name
                    FROM sys.key_constraints k
                    INNER JOIN sys.tables t ON k.parent_object_id = t.object_id
                    INNER JOIN sys.index_columns ic ON ic.object_id = t.object_id AND ic.index_id = k.unique_index_id
                    INNER JOIN sys.columns kc ON kc.object_id = t.object_id AND kc.column_id = ic.column_id
                    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
                    WHERE k.type = 'PK' AND  t.object_id = OBJECT_ID(@pTableName)
                )
                select @missing_pk = name
                from (
                        SELECT name
                        FROM CTE_pk
                        EXCEPT
                        SELECT k2.[key] COLLATE SQL_Latin1_General_CP1_CI_AS AS [key]
                        FROM OPENJSON( @pJson) k1
                        CROSS APPLY OPENJSON(k1.value) k2
                    )a

                IF (LEN(ISNULL(@primary_key,'')) > 0)
                BEGIN
                    IF (LEN(ISNULL(@missing_pk,'')) = 0)
                    BEGIN
                        DECLARE @sql nvarchar(max) = N'
                        MERGE ' + @pTableName + ' AS t
                        USING (SELECT j.* FROM OPENJSON(''' + @pJson + ''') WITH (' + @json_columns + ') AS j) AS s
                        ON ' + @primary_key + '
                        WHEN MATCHED THEN UPDATE SET ' + @update_columns + '
                        WHEN NOT MATCHED THEN INSERT (' + @insert_columns + ') VALUES (' + @insert_columns + ')
                        OPTION (RECOMPILE);
                      ';
                        print @sql
                        EXEC sp_executesql @sql, N'@pJson nvarchar(max)', @pJson = @pJson;
                    END
                    ELSE
                    BEGIN
                        PRINT 'Source missing primary key column: ' + ISNULL(@missing_pk,'')
                    END
                END
                ELSE
                BEGIN
                    PRINT 'Target table requires primary key'
                END
            END
            ELSE
            BEGIN
                PRINT 'Invalid column'
            END
        END
        ELSE
        BEGIN
            PRINT 'Invalid table'
        END
    END
    ELSE
    BEGIN
        PRINT 'Invalid JSON'
    END
END