CREATE PROCEDURE System.sp_generate_merge
(
 @table_name varchar(776),                  -- The table/view for which the MERGE statement will be generated using the existing data
 @target_table varchar(776) = NULL,         -- Use this parameter to specify a different table name into which the data will be inserted/updated/deleted
 @from varchar(800) = NULL,                 -- Use this parameter to filter the rows based on a filter condition (using WHERE)
 @include_timestamp bit = 0,                -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the MERGE statement
 @debug_mode bit = 0, -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
 @schema varchar(64) = NULL, -- Use this parameter if you are not the owner of the table
 @ommit_images bit = 0, -- Use this parameter to generate MERGE statement by omitting the 'image' columns
 @ommit_identity bit = 0, -- Use this parameter to ommit the identity columns
 @top int = NULL, -- Use this parameter to generate a MERGE statement only for the TOP n rows
 @cols_to_include varchar(8000) = NULL, -- List of columns to be included in the MERGE statement
 @cols_to_exclude varchar(8000) = NULL, -- List of columns to be excluded from the MERGE statement
 @update_only_if_changed bit = 1, -- When 1, only performs an UPDATE operation if an included column in a matched row has changed.
 @disable_constraints bit = 0, -- When 1, disables foreign key constraints and enables them after the MERGE statement
 @ommit_computed_cols bit = 0, -- When 1, computed columns will not be included in the MERGE statement
 @pk_constraint_type varchar(50) = 'PRIMARY KEY',  -- CONSTRAINT_TYPE = 'UNIQUE' - use if @ommit_identity = 1
 @fk_replace_ColumnList varchar(max) = NULL,  --  @Delimiter = '|'
 @fk_replace_ValuesList varchar(max) = NULL   --  @Delimiter = '|'
)
AS
BEGIN
    -- ver1-0   2017-11-16

/***********************************************************************************************************
Example 1:
    EXEC System.sp_generate_merge @schema = 'Export',
    @table_name ='Dict',
    @update_only_if_changed = 0,
    @ommit_identity = 1,
    @debug_mode = 1,
    @pk_constraint_type = 'UNIQUE',
    @fk_replace_ColumnList = 'DictType_ID|DataType_ID',
    @fk_replace_ValuesList =
' ''(Select sub.ID
        From Export.DictType sub
        Where sub.Code = '''''' + (Select sub.Code From Showcase.DictType sub Where sub.Id = main.[DictType_ID]) + ''''''
 )''
|
  ''(Select sub.ID
        From Export.DataType sub
        Where sub.Code = '''''' + (Select sub.Code From XBRL.DataType sub Where sub.Id = main.[DataType_ID]) + ''''''
 )''
'

***********************************************************************************************************/

SET NOCOUNT ON


--Making sure user only uses either @cols_to_include or @cols_to_exclude
IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL))
 BEGIN
 RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1)
 RETURN -1 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified
 END


--Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format
IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0))
 BEGIN
 RAISERROR('Invalid use of @cols_to_include property',16,1)
 PRINT 'Specify column names surrounded by single quotes and separated by commas'
 PRINT 'Eg: EXEC sp_generate_merge titles, @cols_to_include = "''title_id'',''title''"'
 RETURN -1 --Failure. Reason: Invalid use of @cols_to_include property
 END

IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0))
 BEGIN
 RAISERROR('Invalid use of @cols_to_exclude property',16,1)
 PRINT 'Specify column names surrounded by single quotes and separated by commas'
 PRINT 'Eg: EXEC sp_generate_merge titles, @cols_to_exclude = "''title_id'',''title''"'
 RETURN -1 --Failure. Reason: Invalid use of @cols_to_exclude property
 END


--Checking to see if the database name is specified along wih the table name
--Your database context should be local to the table for which you want to generate a MERGE statement
--specifying the database name is not allowed
IF (PARSENAME(@table_name,3)) IS NOT NULL
 BEGIN
 RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1)
 RETURN -1 --Failure. Reason: Database name is specified along with the table name, which is not allowed
 END


--Checking for the existence of 'user table' or 'view'
--This procedure is not written to work on system tables
--To script the data in system tables, just create a view on the system tables and script the view instead
IF @schema IS NULL
 BEGIN
 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') AND TABLE_SCHEMA = SCHEMA_NAME())
 BEGIN
 RAISERROR('User table or view not found.',16,1)
 PRINT 'You may see this error if the specified table is not in your default schema (' + SCHEMA_NAME() + '). In that case use @schema parameter to specify the schema name.'
 PRINT 'Make sure you have SELECT permission on that table or view.'
 RETURN -1 --Failure. Reason: There is no user table or view with this name
 END
 END
ELSE
 BEGIN
 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') AND TABLE_SCHEMA = @schema)
 BEGIN
 RAISERROR('User table or view not found.',16,1)
 PRINT 'Make sure you have SELECT permission on that table or view.'
 RETURN -1 --Failure. Reason: There is no user table or view with this name
 END
 END


--Variable declarations
DECLARE @Column_ID                 int,
        @Column_List               varchar(8000),
        @Column_List_For_Update    varchar(8000),
        @Column_List_For_Check     varchar(8000),
        @Column_Name               varchar(128),
        @Column_Name_Unquoted      varchar(128),
        @Data_Type                 varchar(128),
        @Actual_Values             varchar(8000), --This is the string that will be finally executed to generate a MERGE statement
        @IDN                       varchar(128), --Will contain the IDENTITY column's name in the table
        @Target_Table_For_Output   varchar(776),
        @Source_Table_Qualified    varchar(776),
        @SqlValuesText             varchar(Max),
        @SqlMergeText              varchar(Max),
        @Delimiter                 char(1),
        @fk_idx                    int


CREATE TABLE #tmp_values
    (val varchar(max))

--Variable Initialization
SET @IDN = ''
SET @Column_ID = 0
SET @Column_Name = ''
SET @Column_Name_Unquoted = ''
SET @Column_List = ''
SET @Column_List_For_Update = ''
SET @Column_List_For_Check = ''
SET @Actual_Values = ''
SET @SqlMergeText = ''
SET @Delimiter = '|'

--Variable Defaults
IF @schema IS NULL
 BEGIN
 SET @Target_Table_For_Output = QUOTENAME(COALESCE(@target_table, @table_name))
 END
ELSE
 BEGIN
 SET @Target_Table_For_Output = QUOTENAME(@schema) + '.' + QUOTENAME(COALESCE(@target_table, @table_name))
 END

SET @Source_Table_Qualified = QUOTENAME(COALESCE(@schema,SCHEMA_NAME())) + '.' + QUOTENAME(@table_name)

--To get the first column's ID
SELECT @Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name
AND TABLE_SCHEMA = COALESCE(@schema, SCHEMA_NAME())


--Loop through all the columns of the table, to get the column names and their data types
WHILE @Column_ID IS NOT NULL
 BEGIN
 SELECT @Column_Name = QUOTENAME(COLUMN_NAME),
 @Column_Name_Unquoted = COLUMN_NAME,
 @Data_Type = DATA_TYPE
 FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
 WHERE ORDINAL_POSITION = @Column_ID
 AND TABLE_NAME = @table_name
 AND TABLE_SCHEMA = COALESCE(@schema, SCHEMA_NAME())

 IF @cols_to_include IS NOT NULL --Selecting only user specified columns
 BEGIN
 IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0
 BEGIN
 GOTO SKIP_LOOP
 END
 END

 IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns
 BEGIN
 IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0
 BEGIN
 GOTO SKIP_LOOP
 END
 END

 --Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
 IF (SELECT COLUMNPROPERTY( OBJECT_ID(@Source_Table_Qualified),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1
 BEGIN
 IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column
 SET @IDN = @Column_Name
 ELSE
 GOTO SKIP_LOOP
 END

 --Making sure whether to output computed columns or not
 IF @ommit_computed_cols = 1
 BEGIN
 IF (SELECT COLUMNPROPERTY( OBJECT_ID(@Source_Table_Qualified),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsComputed')) = 1
 BEGIN
 GOTO SKIP_LOOP
 END
 END

 --Tables with columns of IMAGE data type are not supported for obvious reasons
 IF(@Data_Type in ('image'))
 BEGIN
 IF (@ommit_images = 0)
 BEGIN
 RAISERROR('Tables with image columns are not supported.',16,1)
 PRINT 'Use @ommit_images = 1 parameter to generate a MERGE for the rest of the columns.'
 RETURN -1 --Failure. Reason: There is a column with image data type
 END
 ELSE
 BEGIN
 GOTO SKIP_LOOP
 END
 END


 SET @fk_idx = 0

 Select @fk_idx = [Index]
    From System.SplitString(@fk_replace_ColumnList,@Delimiter)
    Where Item = @Column_Name_Unquoted

 --Determining the data type of the column and depending on the data type, the VALUES part of
 --the MERGE statement is generated. Care is taken to handle columns with NULL values. Also
 --making sure, not to lose any data from flot, real, money, smallmomey, datetime columns
 SET @Actual_Values = @Actual_Values +
 + Case when ISNULL(@fk_idx,0) > 0
        then (Select top 1 [Item]
                From System.SplitString(@fk_replace_ValuesList,@Delimiter)
                Where [Index] = @fk_idx)
        else  CASE WHEN @Data_Type IN ('char','nchar')
                       THEN 'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
                   WHEN @Data_Type IN ('varchar','nvarchar')
                       THEN 'COALESCE('''''''' + REPLACE(' + @Column_Name + ','''''''','''''''''''')+'''''''',''NULL'')'
                   WHEN @Data_Type IN ('time','date','datetime','smalldatetime','datetime2')
                       THEN 'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',127))+'''''''',''NULL'')'
                   WHEN @Data_Type IN ('uniqueidentifier')
                       THEN 'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'
                   WHEN @Data_Type IN ('text')
                       THEN 'COALESCE('''''''' + REPLACE(CONVERT(varchar(max),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
                   WHEN @Data_Type IN ('ntext')
                       THEN 'COALESCE('''''''' + REPLACE(CONVERT(nvarchar(max),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
                   WHEN @Data_Type IN ('binary','varbinary')
                       THEN 'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
                   WHEN @Data_Type IN ('timestamp','rowversion')
                       THEN CASE WHEN @include_timestamp = 0
                                 THEN '''DEFAULT'''
                                 ELSE 'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
                            END
                   WHEN @Data_Type IN ('float','real','money','smallmoney')
                       THEN 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ',2)' + ')),''NULL'')'
                   ELSE  'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ')' + ')),''NULL'')'
              END
    end
 + '+' + ''',''' + ' + '

 --Generating the column list for the MERGE statement
 SET @Column_List = @Column_List + @Column_Name + ','

 --Don't update Primary Key or Identity columns
 IF NOT EXISTS(
 SELECT 1
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
 INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
 WHERE pk.TABLE_NAME = @table_name
 AND pk.TABLE_SCHEMA = COALESCE(@schema, SCHEMA_NAME())
 AND CONSTRAINT_TYPE = 'PRIMARY KEY'
 AND c.TABLE_NAME = pk.TABLE_NAME
 AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
 AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 AND c.COLUMN_NAME = @Column_Name_Unquoted
 )
 BEGIN
 SET @Column_List_For_Update = @Column_List_For_Update + @Column_Name + ' = Source.' + @Column_Name + ',
'
 SET @Column_List_For_Check = @Column_List_For_Check +
 CASE @Data_Type
 WHEN 'text' THEN 'CAST(Target.' + @Column_Name + ' AS varchar(max)) <> CAST(Source.' + @Column_Name + ' AS varchar(max)) OR '
 WHEN 'ntext' THEN 'CAST(Target.' + @Column_Name + ' AS nvarchar(max)) <> CAST(Source.' + @Column_Name + ' AS nvarchar(max)) OR '
 ELSE 'Target.' + @Column_Name + ' <> Source.' + @Column_Name + ' OR '
 END
 END

 SKIP_LOOP: --The label used in GOTO

 SELECT @Column_ID = MIN(ORDINAL_POSITION)
 FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
 WHERE TABLE_NAME = @table_name
 AND TABLE_SCHEMA = COALESCE(@schema, SCHEMA_NAME())
 AND ORDINAL_POSITION > @Column_ID

 END --Loop ends here!


--To get rid of the extra characters that got concatenated during the last run through the loop
IF LEN(@Column_List_For_Update) <> 0
 BEGIN
 SET @Column_List_For_Update = ' ' + LEFT(@Column_List_For_Update,len(@Column_List_For_Update) - 4)
 END

IF LEN(@Column_List_For_Check) <> 0
 BEGIN
 SET @Column_List_For_Check = LEFT(@Column_List_For_Check,len(@Column_List_For_Check) - 3)
 END

SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)

SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)
IF LEN(LTRIM(@Column_List)) = 0
 BEGIN
 RAISERROR('No columns to select. There should at least be one column to generate the output',16,1)
 RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter
 END


--Get the join columns ----------------------------------------------------------
DECLARE @PK_column_list VARCHAR(8000)
DECLARE @PK_column_joins VARCHAR(8000)
SET @PK_column_list = ''
SET @PK_column_joins = ''

SELECT @PK_column_list = @PK_column_list + QUOTENAME(c.COLUMN_NAME) + ', '
      ,@PK_column_joins = @PK_column_joins + 'Target.[' + c.COLUMN_NAME + '] = Source.[' + c.COLUMN_NAME + '] AND '
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @table_name
AND pk.TABLE_SCHEMA = COALESCE(@schema, SCHEMA_NAME())
AND CONSTRAINT_TYPE = @pk_constraint_type
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

SET @PK_column_list = LEFT(@PK_column_list, LEN(@PK_column_list) -1)
SET @PK_column_joins = LEFT(@PK_column_joins, LEN(@PK_column_joins) -4)


--Forming the final string that will be executed, to output the a MERGE statement
SET @Actual_Values =
 'SELECT ' +
 CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +
 '''' +
 ' (''+ ' + @Actual_Values + '+'')''' + ' ' +
 COALESCE(@from,' FROM ' + @Source_Table_Qualified + ' (NOLOCK) as main
 ')


--Determining whether to ouput any debug information
IF @debug_mode =1
 BEGIN
 PRINT '/*****START OF DEBUG INFORMATION*****'
 PRINT ''
 PRINT 'The primary key column list:'
 PRINT @PK_column_list
 PRINT ''
 PRINT 'The INSERT column list:'
 PRINT @Column_List
 PRINT ''
 PRINT 'The UPDATE column list:'
 PRINT @Column_List_For_Update
 PRINT ''
 PRINT 'The SELECT statement executed to generate the MERGE:'
 PRINT @Actual_Values
 PRINT ''
 PRINT '*****END OF DEBUG INFORMATION*****/'
 PRINT ''
 END

PRINT '--MERGE generated by ''sp_generate_merge'' stored procedure, Version 0.9'

Set @SqlMergeText = @SqlMergeText + char(13) + 'SET NOCOUNT ON'

--Determining whether to print IDENTITY_INSERT or not
IF (LEN(@IDN) <> 0)
 BEGIN
 Set @SqlMergeText = @SqlMergeText + char(13) + 'SET IDENTITY_INSERT ' + @Target_Table_For_Output + ' ON'
 END


--Temporarily disable constraints on the target table
IF @disable_constraints = 1 AND (OBJECT_ID(@Source_Table_Qualified, 'U') IS NOT NULL)
 BEGIN
 Set @SqlMergeText = @SqlMergeText + char(13) + 'ALTER TABLE ' + @Target_Table_For_Output + ' NOCHECK CONSTRAINT ALL' --Code to disable constraints temporarily
 END


--Output the start of the MERGE statement, qualifying with the schema name only if the caller explicitly specified it
Set @SqlMergeText = @SqlMergeText + char(13) + 'MERGE INTO ' + @Target_Table_For_Output + ' AS Target'
Set @SqlMergeText = @SqlMergeText + char(13) + 'USING (VALUES'


--All the hard work pays off here!!! You'll get your MERGE statement, when the next line executes!

Insert into #tmp_values (val)
    EXEC (@Actual_Values)

Set @SqlValuesText = NULL

Select @SqlValuesText = COALESCE(', ' + @SqlValuesText , ' ') + val + char(13)
    From #tmp_values
    WHERE rtrim(ltrim(val)) != ''


--Output the columns to correspond with each of the values above--------------------
Set @SqlMergeText = @SqlMergeText
                    + char(13)
                    + @SqlValuesText
                    + ') AS Source (' + @Column_List + ')'


--Output the join columns ----------------------------------------------------------
Set @SqlMergeText = @SqlMergeText + char(13) + 'ON (' + @PK_column_joins + ')'


--When matched, perform an UPDATE on any metadata columns only (ie. not on PK)------
IF LEN(@Column_List_For_Update) <> 0
BEGIN
 Set @SqlMergeText = @SqlMergeText + char(13) + 'WHEN MATCHED ' + CASE WHEN @update_only_if_changed = 1 THEN 'AND (' + @Column_List_For_Check + ') ' ELSE '' END + 'THEN'
 Set @SqlMergeText = @SqlMergeText + char(13) + ' UPDATE SET'
 Set @SqlMergeText = @SqlMergeText + char(13) + @Column_List_For_Update
END


--When NOT matched by target, perform an INSERT------------------------------------
Set @SqlMergeText = @SqlMergeText + char(13) + 'WHEN NOT MATCHED BY TARGET THEN';
Set @SqlMergeText = @SqlMergeText + char(13) + ' INSERT(' + @Column_List + ')'
Set @SqlMergeText = @SqlMergeText + char(13) + ' VALUES(' + REPLACE(@Column_List, '[', 'Source.[') + ')'

/*
-- skip Delete
--When NOT matched by source, DELETE the row
Set @SqlMergeText = @SqlMergeText + char(13) + 'WHEN NOT MATCHED BY SOURCE THEN '
Set @SqlMergeText = @SqlMergeText + char(13) + ' DELETE;'
Set @SqlMergeText = @SqlMergeText + char(13) + ''
Set @SqlMergeText = @SqlMergeText + char(13) + 'GO'
*/

SET @SqlMergeText = @SqlMergeText + ';' + char(13) + 'GO'


/*
-- skip errors
--Display the number of affected rows to the user, or report if an error occurred---
Set @SqlMergeText = @SqlMergeText + char(13) + 'DECLARE @mergeError int'
Set @SqlMergeText = @SqlMergeText + char(13) + ' , @mergeCount int'
Set @SqlMergeText = @SqlMergeText + char(13) + 'SELECT @mergeError = @@ERROR, @mergeCount = @@ROWCOUNT'
Set @SqlMergeText = @SqlMergeText + char(13) + 'IF @mergeError != 0'
Set @SqlMergeText = @SqlMergeText + char(13) + ' BEGIN'
Set @SqlMergeText = @SqlMergeText + char(13) + ' Set @SqlMergeText = @SqlMergeText + char(13) + ''ERROR OCCURRED IN MERGE FOR ' + @Target_Table_For_Output + '. Rows affected: '' + CAST(@mergeCount AS VARCHAR(100)); -- SQL should always return zero rows affected';
Set @SqlMergeText = @SqlMergeText + char(13) + ' END'
Set @SqlMergeText = @SqlMergeText + char(13) + 'ELSE'
Set @SqlMergeText = @SqlMergeText + char(13) + ' BEGIN'
Set @SqlMergeText = @SqlMergeText + char(13) + ' Set @SqlMergeText = @SqlMergeText + char(13) + ''' + @Target_Table_For_Output + ' rows affected by MERGE: '' + CAST(@mergeCount AS VARCHAR(100));';
Set @SqlMergeText = @SqlMergeText + char(13) + ' END'
Set @SqlMergeText = @SqlMergeText + char(13) + 'GO'
*/

--Re-enable the previously disabled constraints-------------------------------------
IF @disable_constraints = 1 AND (OBJECT_ID(@Source_Table_Qualified, 'U') IS NOT NULL)
 BEGIN
    Set @SqlMergeText = @SqlMergeText + char(13) + 'ALTER TABLE ' + @Target_Table_For_Output + ' CHECK CONSTRAINT ALL' --Code to enable the previously disabled constraints
 END


--Switch-off identity inserting------------------------------------------------------
IF (LEN(@IDN) <> 0)
 BEGIN
 Set @SqlMergeText = @SqlMergeText + char(13) + 'SET IDENTITY_INSERT ' + @Target_Table_For_Output + ' OFF'
 END


Set @SqlMergeText = @SqlMergeText + char(13) + 'SET NOCOUNT OFF'
Set @SqlMergeText = @SqlMergeText + char(13) + 'GO'

SET NOCOUNT OFF

Select @SqlMergeText

RETURN 0 --Success. We are done!
END
GO