Skip to content

Instantly share code, notes, and snippets.

@EitanBlumin

EitanBlumin/Generate_Merge_For_All_Tables.sql

Last active June 26, 2023 03:22
Show Gist options
  • Save EitanBlumin/7faba0b39c4f90d4cfa879a45f3e01eb to your computer and use it in GitHub Desktop.
Save EitanBlumin/7faba0b39c4f90d4cfa879a45f3e01eb to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. EitanBlumin revised this gist Mar 17, 2020. No changes.
  2. EitanBlumin revised this gist Dec 18, 2018. 1 changed file with 21 additions and 6 deletions.
    27 changes: 21 additions & 6 deletions Generate_Merge_For_Table.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -131,7 +131,7 @@ END
    SELECT
    @CurrColumnId = MIN(ORDINAL_POSITION)
    FROM
    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
    INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK)
    WHERE
    TABLE_NAME = @CurrTable
    AND TABLE_SCHEMA = @CurrSchema
    @@ -144,7 +144,7 @@ BEGIN
    @CurrColumnName = QUOTENAME(COLUMN_NAME),
    @CurrColumnType = DATA_TYPE
    FROM
    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
    INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK)
    WHERE
    ORDINAL_POSITION = @CurrColumnId
    AND TABLE_NAME = @CurrTable
    @@ -166,7 +166,7 @@ BEGIN
    SET @GetValues = ISNULL( @GetValues + ' + '',''' , '''(''' ) + ' + ' +
    CASE
    -- Format column value retrieval based on its data type
    WHEN @CurrColumnType IN ('text','char','varchar')
    WHEN @CurrColumnType IN ('text','char','varchar','time')
    THEN
    'COALESCE('''''''' + REPLACE(CONVERT(nvarchar(max),' + @CurrColumnName + '),'''''''','''''''''''')+'''''''',''NULL'')'
    WHEN @CurrColumnType IN ('ntext','nchar','nvarchar','xml')
    @@ -205,9 +205,24 @@ BEGIN
    BEGIN
    SET @HasIdentity = 1
    END
    ELSE
    ELSE IF NOT EXISTS
    (
    SELECT
    NULL
    FROM
    sys.indexes AS ind
    INNER JOIN
    sys.index_columns AS indcol
    ON
    ind.object_id = indcol.object_id
    AND ind.index_id = indcol.index_id
    WHERE
    ind.is_primary_key = 1
    AND ind.object_id = OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable))
    AND indcol.column_id = @CurrColumnId
    )
    BEGIN
    -- If column is not IDENTITY, concatenate it to UPDATE SET clause
    -- If column is not IDENTITY and not part of the PK, then concatenate it to UPDATE SET clause
    SET @UpdateSet = ISNULL(@UpdateSet + N'
    , ', N'') + @CurrColumnName + N' = src.' + @CurrColumnName
    END
    @@ -218,7 +233,7 @@ BEGIN
    SELECT
    @CurrColumnId = MIN(ORDINAL_POSITION)
    FROM
    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
    INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK)
    WHERE
    ORDINAL_POSITION > @CurrColumnId
    AND TABLE_NAME = @CurrTable
  3. EitanBlumin revised this gist Nov 18, 2018. 1 changed file with 0 additions and 345 deletions.
    345 changes: 0 additions & 345 deletions usp_Generate_Merge_For_Table.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,345 +0,0 @@
    /*
    ===================================================
    Generate MERGE Statement for a Table
    ===================================================
    Copyright: Eitan Blumin (C) 2012
    Email: [email protected]
    Source: www.madeiradata.com
    Disclaimer:
    The author is not responsible for any damage this
    script or any of its variations may cause.
    Do not execute it or any variations of it on production
    environments without first verifying its validity
    on controlled testing and/or QA environments.
    You may use this script at your own risk and may change it
    to your liking, as long as you leave this disclaimer header
    fully intact and unchanged.
    Description:
    This procedure generates a MERGE statement for the table provided as a parameter,
    using the records currently in the table.
    The MERGE statement is returned in a query as a single row and column.
    If there are no rows in the table, a simple DELETE statement will be generated instead.
    Instructions:
    The resulting script from this procedure can be used to "initialize" or "reset"
    the desired table to the state it was in during the script generation.
    For example: You're about to perform tests on your table and you wish to have
    a "starting point" to which you can reset the table at any time.
    Before starting your tests, you run this procedure to generate the initialization
    script for the table and save it somewhere. You do your tests, and at any time
    you can execute the script you saved to reset the table back to its state
    at the beginning of the tests (of course, strictly in terms of data - not structure).
    Important Note:
    Tables that don't have a primary key are not supported!
    */

    IF OBJECT_ID('usp_Generate_Merge_For_Table', 'P') IS NOT NULL
    BEGIN
    PRINT 'Dropping existing procedure...'
    DROP PROCEDURE usp_Generate_Merge_For_Table
    END

    PRINT 'Creating procedure...'
    GO
    /*
    Sample usage:
    EXEC usp_Generate_Merge_For_Table 'tbl_forms', 'dbo'
    */
    CREATE PROCEDURE [dbo].[usp_Generate_Merge_For_Table]
    @CurrTable SYSNAME, -- table name
    @CurrSchema SYSNAME = 'dbo', -- table schema name

    @delete_unmatched_rows BIT = 1, -- enable/disable DELETION of rows
    @update_existing_rows BIT = 1, -- enable/disable UPDATE of rows
    @insert_new_rows BIT = 1, -- enable/disable INSERT of rows
    @debug_mode BIT = 0, -- enable/disable debug mode
    @include_timestamp BIT = 0, -- include timestamp columns or not
    @ommit_computed_cols BIT = 1, -- ommit computed columns or not (in case target table doesn't have computed columns)
    @top_clause NVARCHAR(4000) = N'TOP 100 PERCENT' -- you can use this to limit number of generate rows (e.g. TOP 200)
    AS

    SET NOCOUNT ON;

    -- Variable declaration
    DECLARE
    @MergeStmnt NVARCHAR(MAX),

    @CurrColumnId INT,
    @CurrColumnName SYSNAME,
    @CurrColumnType VARCHAR(1000),
    @ColumnList NVARCHAR(MAX),
    @UpdateSet NVARCHAR(MAX),
    @PKJoinClause NVARCHAR(MAX),
    @HasIdentity BIT,
    @GetValues NVARCHAR(MAX),
    @Values NVARCHAR(MAX)

    -- Init variables
    SELECT
    @CurrColumnId = NULL,
    @CurrColumnName = NULL,
    @CurrColumnType = NULL,
    @MergeStmnt = NULL,
    @ColumnList = NULL,
    @UpdateSet = NULL,
    @PKJoinClause = NULL,
    @GetValues = NULL,
    @Values = NULL,
    @HasIdentity = 0

    -- Make sure table exists
    IF OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable)) IS NULL
    BEGIN
    RAISERROR(N'ERROR: Table [%s].[%s] not found.', 16, 1, @CurrSchema, @CurrTable) WITH NOWAIT;
    GOTO Quit;
    END

    -- find the table's Primary Key column(s) to build a JOIN clause
    SELECT
    @PKJoinClause = ISNULL(@PKJoinClause + N'
    AND ',N'') + 'trgt.' + QUOTENAME(col.name) + N' = src.' + QUOTENAME(col.name)
    FROM
    sys.indexes AS ind
    INNER JOIN
    sys.index_columns AS indcol
    ON
    ind.object_id = indcol.object_id
    AND ind.index_id = indcol.index_id
    INNER JOIN
    sys.columns AS col
    ON
    ind.object_id = col.object_id
    AND indcol.column_id = col.column_id
    WHERE
    ind.is_primary_key = 1
    AND ind.object_id = OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable))

    IF @debug_mode = 1
    PRINT 'PK Join Clause:
    ' + @PKJoinClause

    -- If nothing found, abort (table is not supported)
    IF @PKJoinClause IS NULL
    BEGIN
    RAISERROR(N'ERROR: Table %s is not supported because it''s missing a Primary Key.', 16, 1, @CurrTable) WITH NOWAIT;
    GOTO Quit;
    END

    -- start with the first column
    SELECT
    @CurrColumnId = MIN(ORDINAL_POSITION)
    FROM
    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
    WHERE
    TABLE_NAME = @CurrTable
    AND TABLE_SCHEMA = @CurrSchema


    -- loop through all the table columns, to get the column names and their data types
    WHILE @CurrColumnId IS NOT NULL
    BEGIN
    SELECT
    @CurrColumnName = QUOTENAME(COLUMN_NAME),
    @CurrColumnType = DATA_TYPE
    FROM
    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
    WHERE
    ORDINAL_POSITION = @CurrColumnId
    AND TABLE_NAME = @CurrTable
    AND TABLE_SCHEMA = @CurrSchema

    IF @debug_mode = 1
    PRINT 'Processing column ' + @CurrColumnName

    -- Choosing whether to output computed columns or not
    IF @ommit_computed_cols = 1
    BEGIN
    IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + @CurrTable),SUBSTRING(@CurrColumnName,2,LEN(@CurrColumnName) - 2),'IsComputed')) = 1
    BEGIN
    GOTO SKIP_COLUMN
    END
    END

    -- Concatenate column value selection to the values list
    SET @GetValues = ISNULL( @GetValues + ' + '',''' , '''(''' ) + ' + ' +
    CASE
    -- Format column value retrieval based on its data type
    WHEN @CurrColumnType IN ('text','char','varchar')
    THEN
    'COALESCE('''''''' + REPLACE(CONVERT(nvarchar(max),' + @CurrColumnName + '),'''''''','''''''''''')+'''''''',''NULL'')'
    WHEN @CurrColumnType IN ('ntext','nchar','nvarchar','xml')
    THEN
    'COALESCE(''N'''''' + REPLACE(CONVERT(nvarchar(max),' + @CurrColumnName + '),'''''''','''''''''''')+'''''''',''NULL'')'
    WHEN @CurrColumnType LIKE '%date%'
    THEN
    'COALESCE('''''''' + RTRIM(CONVERT(varchar(max),' + @CurrColumnName + ',109))+'''''''',''NULL'')'
    WHEN @CurrColumnType IN ('uniqueidentifier')
    THEN
    'COALESCE('''''''' + REPLACE(CONVERT(varchar(255),RTRIM(' + @CurrColumnName + ')),'''''''','''''''''''')+'''''''',''NULL'')'
    WHEN @CurrColumnType IN ('binary','varbinary','image')
    THEN
    'COALESCE(RTRIM(CONVERT(nvarchar(max),' + @CurrColumnName + ',1)),''NULL'')'
    WHEN @CurrColumnType IN ('timestamp','rowversion')
    THEN
    CASE
    WHEN @include_timestamp = 0
    THEN
    '''DEFAULT'''
    ELSE
    'COALESCE(RTRIM(CONVERT(varchar(max),' + 'CONVERT(int,' + @CurrColumnName + '))),''NULL'')'
    END
    WHEN @CurrColumnType IN ('float','real','money','smallmoney')
    THEN
    'COALESCE(LTRIM(RTRIM(' + 'CONVERT(varchar(max), ' + @CurrColumnName + ',2)' + ')),''NULL'')'
    ELSE
    'COALESCE(LTRIM(RTRIM(' + 'CONVERT(varchar(max), ' + @CurrColumnName + ')' + ')),''NULL'')'
    END

    -- Concatenate column name to column list
    SET @ColumnList = ISNULL(@ColumnList + N',',N'') + @CurrColumnName

    -- Make sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
    IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + @CurrTable),SUBSTRING(@CurrColumnName,2,LEN(@CurrColumnName) - 2),'IsIdentity')) = 1
    BEGIN
    SET @HasIdentity = 1
    END
    ELSE
    BEGIN
    -- If column is not IDENTITY, concatenate it to UPDATE SET clause
    SET @UpdateSet = ISNULL(@UpdateSet + N'
    , ', N'') + @CurrColumnName + N' = src.' + @CurrColumnName
    END

    SKIP_COLUMN: -- The label used in GOTO to skip column

    -- Get next column in order
    SELECT
    @CurrColumnId = MIN(ORDINAL_POSITION)
    FROM
    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
    WHERE
    ORDINAL_POSITION > @CurrColumnId
    AND TABLE_NAME = @CurrTable
    AND TABLE_SCHEMA = @CurrSchema


    -- Column loop ends here
    END

    -- Finalize VALUES constructor
    SET @GetValues = @GetValues + ' + '')'' ';

    IF @debug_mode = 1
    PRINT 'Values Retrieval:
    ' + @GetValues + '
    ';

    -- Using everything we found above, save all the table records as a values constructor (using dynamic SQL)
    DECLARE @Params NVARCHAR(MAX)
    DECLARE @CMD NVARCHAR(MAX);

    SET @Params = N'@Result NVARCHAR(MAX) OUTPUT'
    SET @CMD = 'SELECT ' + @top_clause + N'
    @Result = ISNULL(@Result + '',
    '','''') + ' + @GetValues + ' FROM ' + QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable)

    IF @debug_mode = 1
    SELECT @CMD;

    -- Execute command and get the @Values parameter as output
    EXECUTE sp_executesql @CMD, @Params, @Values OUTPUT

    -- If table returned no rows, then there's nothing to output other than deletion command
    IF @@ROWCOUNT = 0 OR @Values IS NULL
    BEGIN
    -- If deletion is enabled
    IF @delete_unmatched_rows = 1
    -- Generate a simple DELETE statement
    SET @MergeStmnt = N'DELETE FROM ' + QUOTENAME(@CurrSchema) + N'.' + QUOTENAME(@CurrTable)
    ELSE
    -- Otherwise, generate an empty script
    SET @MergeStmnt = N''
    END
    ELSE
    -- Otherwise, build the MERGE statement
    BEGIN

    -- Use IDENTITY_INSERT if table has an identity column
    IF @HasIdentity = 1
    SET @MergeStmnt = 'SET IDENTITY_INSERT ' + QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable) + ' ON;'
    ELSE
    SET @MergeStmnt = N''

    -- Build the MERGE statement using all the parts we found
    SET @MergeStmnt = @MergeStmnt + N'
    MERGE INTO ' + QUOTENAME(@CurrSchema) + N'.' + QUOTENAME(@CurrTable) + N' AS trgt
    USING (VALUES
    ' + @Values + N'
    ) AS src(' + @ColumnList + N')
    ON
    ' + @PKJoinClause
    + CASE WHEN @update_existing_rows = 1 THEN N'
    WHEN MATCHED THEN
    UPDATE SET
    ' + @UpdateSet
    ELSE N'' END
    + CASE WHEN @insert_new_rows = 1 THEN N'
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (' + @ColumnList + N')
    VALUES (' + @ColumnList + N')'
    ELSE N'' END
    + CASE WHEN @delete_unmatched_rows = 1 THEN N'
    WHEN NOT MATCHED BY SOURCE THEN
    DELETE'
    ELSE N'' END + N'
    ;
    ';

    -- Use IDENTITY_INSERT if table has an identity column
    IF @HasIdentity = 1
    SET @MergeStmnt = @MergeStmnt + 'SET IDENTITY_INSERT ' + QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable) + ' OFF;'

    END

    Quit:

    -- Output the final statement

    -- Print long text
    DECLARE
    @CurrStart int,
    @TotalLen int,
    @CurrID int,
    @CurrMsg nvarchar(max)

    SELECT
    @CurrStart = 1,
    @TotalLen = LEN(@MergeStmnt);

    WHILE @CurrStart < @TotalLen
    BEGIN
    -- Find next linebreak
    SET @CurrID = CHARINDEX(CHAR(10),@MergeStmnt,@CurrStart)

    -- If linebreak found
    IF @CurrID > 0
    BEGIN
    -- Trim line from message, print it and increase index
    SET @CurrMsg = SUBSTRING(@MergeStmnt,@CurrStart,@CurrID-@CurrStart-1)
    PRINT @CurrMsg
    SET @CurrStart = @CurrID + 1
    END
    ELSE
    BEGIN
    -- Print last line
    SET @CurrMsg = SUBSTRING(@MergeStmnt,@CurrStart,@TotalLen)
    PRINT @CurrMsg
    SET @CurrStart = @TotalLen
    END
    END

    SELECT @MergeStmnt AS Command;
  4. EitanBlumin revised this gist Aug 28, 2018. No changes.
  5. EitanBlumin revised this gist Aug 28, 2018. 2 changed files with 346 additions and 0 deletions.
    1 change: 1 addition & 0 deletions Generate_Merge_For_All_Tables.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,4 @@

    /*
    ===================================================
    Generate MERGE Statements for All Tables
    345 changes: 345 additions & 0 deletions usp_Generate_Merge_For_Table.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,345 @@
    /*
    ===================================================
    Generate MERGE Statement for a Table
    ===================================================
    Copyright: Eitan Blumin (C) 2012
    Email: [email protected]
    Source: www.madeiradata.com
    Disclaimer:
    The author is not responsible for any damage this
    script or any of its variations may cause.
    Do not execute it or any variations of it on production
    environments without first verifying its validity
    on controlled testing and/or QA environments.
    You may use this script at your own risk and may change it
    to your liking, as long as you leave this disclaimer header
    fully intact and unchanged.
    Description:
    This procedure generates a MERGE statement for the table provided as a parameter,
    using the records currently in the table.
    The MERGE statement is returned in a query as a single row and column.
    If there are no rows in the table, a simple DELETE statement will be generated instead.
    Instructions:
    The resulting script from this procedure can be used to "initialize" or "reset"
    the desired table to the state it was in during the script generation.
    For example: You're about to perform tests on your table and you wish to have
    a "starting point" to which you can reset the table at any time.
    Before starting your tests, you run this procedure to generate the initialization
    script for the table and save it somewhere. You do your tests, and at any time
    you can execute the script you saved to reset the table back to its state
    at the beginning of the tests (of course, strictly in terms of data - not structure).
    Important Note:
    Tables that don't have a primary key are not supported!
    */

    IF OBJECT_ID('usp_Generate_Merge_For_Table', 'P') IS NOT NULL
    BEGIN
    PRINT 'Dropping existing procedure...'
    DROP PROCEDURE usp_Generate_Merge_For_Table
    END

    PRINT 'Creating procedure...'
    GO
    /*
    Sample usage:
    EXEC usp_Generate_Merge_For_Table 'tbl_forms', 'dbo'
    */
    CREATE PROCEDURE [dbo].[usp_Generate_Merge_For_Table]
    @CurrTable SYSNAME, -- table name
    @CurrSchema SYSNAME = 'dbo', -- table schema name

    @delete_unmatched_rows BIT = 1, -- enable/disable DELETION of rows
    @update_existing_rows BIT = 1, -- enable/disable UPDATE of rows
    @insert_new_rows BIT = 1, -- enable/disable INSERT of rows
    @debug_mode BIT = 0, -- enable/disable debug mode
    @include_timestamp BIT = 0, -- include timestamp columns or not
    @ommit_computed_cols BIT = 1, -- ommit computed columns or not (in case target table doesn't have computed columns)
    @top_clause NVARCHAR(4000) = N'TOP 100 PERCENT' -- you can use this to limit number of generate rows (e.g. TOP 200)
    AS

    SET NOCOUNT ON;

    -- Variable declaration
    DECLARE
    @MergeStmnt NVARCHAR(MAX),

    @CurrColumnId INT,
    @CurrColumnName SYSNAME,
    @CurrColumnType VARCHAR(1000),
    @ColumnList NVARCHAR(MAX),
    @UpdateSet NVARCHAR(MAX),
    @PKJoinClause NVARCHAR(MAX),
    @HasIdentity BIT,
    @GetValues NVARCHAR(MAX),
    @Values NVARCHAR(MAX)

    -- Init variables
    SELECT
    @CurrColumnId = NULL,
    @CurrColumnName = NULL,
    @CurrColumnType = NULL,
    @MergeStmnt = NULL,
    @ColumnList = NULL,
    @UpdateSet = NULL,
    @PKJoinClause = NULL,
    @GetValues = NULL,
    @Values = NULL,
    @HasIdentity = 0

    -- Make sure table exists
    IF OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable)) IS NULL
    BEGIN
    RAISERROR(N'ERROR: Table [%s].[%s] not found.', 16, 1, @CurrSchema, @CurrTable) WITH NOWAIT;
    GOTO Quit;
    END

    -- find the table's Primary Key column(s) to build a JOIN clause
    SELECT
    @PKJoinClause = ISNULL(@PKJoinClause + N'
    AND ',N'') + 'trgt.' + QUOTENAME(col.name) + N' = src.' + QUOTENAME(col.name)
    FROM
    sys.indexes AS ind
    INNER JOIN
    sys.index_columns AS indcol
    ON
    ind.object_id = indcol.object_id
    AND ind.index_id = indcol.index_id
    INNER JOIN
    sys.columns AS col
    ON
    ind.object_id = col.object_id
    AND indcol.column_id = col.column_id
    WHERE
    ind.is_primary_key = 1
    AND ind.object_id = OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable))

    IF @debug_mode = 1
    PRINT 'PK Join Clause:
    ' + @PKJoinClause

    -- If nothing found, abort (table is not supported)
    IF @PKJoinClause IS NULL
    BEGIN
    RAISERROR(N'ERROR: Table %s is not supported because it''s missing a Primary Key.', 16, 1, @CurrTable) WITH NOWAIT;
    GOTO Quit;
    END

    -- start with the first column
    SELECT
    @CurrColumnId = MIN(ORDINAL_POSITION)
    FROM
    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
    WHERE
    TABLE_NAME = @CurrTable
    AND TABLE_SCHEMA = @CurrSchema


    -- loop through all the table columns, to get the column names and their data types
    WHILE @CurrColumnId IS NOT NULL
    BEGIN
    SELECT
    @CurrColumnName = QUOTENAME(COLUMN_NAME),
    @CurrColumnType = DATA_TYPE
    FROM
    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
    WHERE
    ORDINAL_POSITION = @CurrColumnId
    AND TABLE_NAME = @CurrTable
    AND TABLE_SCHEMA = @CurrSchema

    IF @debug_mode = 1
    PRINT 'Processing column ' + @CurrColumnName

    -- Choosing whether to output computed columns or not
    IF @ommit_computed_cols = 1
    BEGIN
    IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + @CurrTable),SUBSTRING(@CurrColumnName,2,LEN(@CurrColumnName) - 2),'IsComputed')) = 1
    BEGIN
    GOTO SKIP_COLUMN
    END
    END

    -- Concatenate column value selection to the values list
    SET @GetValues = ISNULL( @GetValues + ' + '',''' , '''(''' ) + ' + ' +
    CASE
    -- Format column value retrieval based on its data type
    WHEN @CurrColumnType IN ('text','char','varchar')
    THEN
    'COALESCE('''''''' + REPLACE(CONVERT(nvarchar(max),' + @CurrColumnName + '),'''''''','''''''''''')+'''''''',''NULL'')'
    WHEN @CurrColumnType IN ('ntext','nchar','nvarchar','xml')
    THEN
    'COALESCE(''N'''''' + REPLACE(CONVERT(nvarchar(max),' + @CurrColumnName + '),'''''''','''''''''''')+'''''''',''NULL'')'
    WHEN @CurrColumnType LIKE '%date%'
    THEN
    'COALESCE('''''''' + RTRIM(CONVERT(varchar(max),' + @CurrColumnName + ',109))+'''''''',''NULL'')'
    WHEN @CurrColumnType IN ('uniqueidentifier')
    THEN
    'COALESCE('''''''' + REPLACE(CONVERT(varchar(255),RTRIM(' + @CurrColumnName + ')),'''''''','''''''''''')+'''''''',''NULL'')'
    WHEN @CurrColumnType IN ('binary','varbinary','image')
    THEN
    'COALESCE(RTRIM(CONVERT(nvarchar(max),' + @CurrColumnName + ',1)),''NULL'')'
    WHEN @CurrColumnType IN ('timestamp','rowversion')
    THEN
    CASE
    WHEN @include_timestamp = 0
    THEN
    '''DEFAULT'''
    ELSE
    'COALESCE(RTRIM(CONVERT(varchar(max),' + 'CONVERT(int,' + @CurrColumnName + '))),''NULL'')'
    END
    WHEN @CurrColumnType IN ('float','real','money','smallmoney')
    THEN
    'COALESCE(LTRIM(RTRIM(' + 'CONVERT(varchar(max), ' + @CurrColumnName + ',2)' + ')),''NULL'')'
    ELSE
    'COALESCE(LTRIM(RTRIM(' + 'CONVERT(varchar(max), ' + @CurrColumnName + ')' + ')),''NULL'')'
    END

    -- Concatenate column name to column list
    SET @ColumnList = ISNULL(@ColumnList + N',',N'') + @CurrColumnName

    -- Make sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
    IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + @CurrTable),SUBSTRING(@CurrColumnName,2,LEN(@CurrColumnName) - 2),'IsIdentity')) = 1
    BEGIN
    SET @HasIdentity = 1
    END
    ELSE
    BEGIN
    -- If column is not IDENTITY, concatenate it to UPDATE SET clause
    SET @UpdateSet = ISNULL(@UpdateSet + N'
    , ', N'') + @CurrColumnName + N' = src.' + @CurrColumnName
    END

    SKIP_COLUMN: -- The label used in GOTO to skip column

    -- Get next column in order
    SELECT
    @CurrColumnId = MIN(ORDINAL_POSITION)
    FROM
    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
    WHERE
    ORDINAL_POSITION > @CurrColumnId
    AND TABLE_NAME = @CurrTable
    AND TABLE_SCHEMA = @CurrSchema


    -- Column loop ends here
    END

    -- Finalize VALUES constructor
    SET @GetValues = @GetValues + ' + '')'' ';

    IF @debug_mode = 1
    PRINT 'Values Retrieval:
    ' + @GetValues + '
    ';

    -- Using everything we found above, save all the table records as a values constructor (using dynamic SQL)
    DECLARE @Params NVARCHAR(MAX)
    DECLARE @CMD NVARCHAR(MAX);

    SET @Params = N'@Result NVARCHAR(MAX) OUTPUT'
    SET @CMD = 'SELECT ' + @top_clause + N'
    @Result = ISNULL(@Result + '',
    '','''') + ' + @GetValues + ' FROM ' + QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable)

    IF @debug_mode = 1
    SELECT @CMD;

    -- Execute command and get the @Values parameter as output
    EXECUTE sp_executesql @CMD, @Params, @Values OUTPUT

    -- If table returned no rows, then there's nothing to output other than deletion command
    IF @@ROWCOUNT = 0 OR @Values IS NULL
    BEGIN
    -- If deletion is enabled
    IF @delete_unmatched_rows = 1
    -- Generate a simple DELETE statement
    SET @MergeStmnt = N'DELETE FROM ' + QUOTENAME(@CurrSchema) + N'.' + QUOTENAME(@CurrTable)
    ELSE
    -- Otherwise, generate an empty script
    SET @MergeStmnt = N''
    END
    ELSE
    -- Otherwise, build the MERGE statement
    BEGIN

    -- Use IDENTITY_INSERT if table has an identity column
    IF @HasIdentity = 1
    SET @MergeStmnt = 'SET IDENTITY_INSERT ' + QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable) + ' ON;'
    ELSE
    SET @MergeStmnt = N''

    -- Build the MERGE statement using all the parts we found
    SET @MergeStmnt = @MergeStmnt + N'
    MERGE INTO ' + QUOTENAME(@CurrSchema) + N'.' + QUOTENAME(@CurrTable) + N' AS trgt
    USING (VALUES
    ' + @Values + N'
    ) AS src(' + @ColumnList + N')
    ON
    ' + @PKJoinClause
    + CASE WHEN @update_existing_rows = 1 THEN N'
    WHEN MATCHED THEN
    UPDATE SET
    ' + @UpdateSet
    ELSE N'' END
    + CASE WHEN @insert_new_rows = 1 THEN N'
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (' + @ColumnList + N')
    VALUES (' + @ColumnList + N')'
    ELSE N'' END
    + CASE WHEN @delete_unmatched_rows = 1 THEN N'
    WHEN NOT MATCHED BY SOURCE THEN
    DELETE'
    ELSE N'' END + N'
    ;
    ';

    -- Use IDENTITY_INSERT if table has an identity column
    IF @HasIdentity = 1
    SET @MergeStmnt = @MergeStmnt + 'SET IDENTITY_INSERT ' + QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable) + ' OFF;'

    END

    Quit:

    -- Output the final statement

    -- Print long text
    DECLARE
    @CurrStart int,
    @TotalLen int,
    @CurrID int,
    @CurrMsg nvarchar(max)

    SELECT
    @CurrStart = 1,
    @TotalLen = LEN(@MergeStmnt);

    WHILE @CurrStart < @TotalLen
    BEGIN
    -- Find next linebreak
    SET @CurrID = CHARINDEX(CHAR(10),@MergeStmnt,@CurrStart)

    -- If linebreak found
    IF @CurrID > 0
    BEGIN
    -- Trim line from message, print it and increase index
    SET @CurrMsg = SUBSTRING(@MergeStmnt,@CurrStart,@CurrID-@CurrStart-1)
    PRINT @CurrMsg
    SET @CurrStart = @CurrID + 1
    END
    ELSE
    BEGIN
    -- Print last line
    SET @CurrMsg = SUBSTRING(@MergeStmnt,@CurrStart,@TotalLen)
    PRINT @CurrMsg
    SET @CurrStart = @TotalLen
    END
    END

    SELECT @MergeStmnt AS Command;
  6. EitanBlumin revised this gist Jul 3, 2018. 2 changed files with 30 additions and 16 deletions.
    6 changes: 3 additions & 3 deletions Generate_Merge_For_All_Tables.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -3,8 +3,8 @@
    Generate MERGE Statements for All Tables
    ===================================================
    Copyright: Eitan Blumin (C) 2012
    Email: eitan@madeira.co.il
    Source: www.madeira.co.il
    Email: eitan@madeiradata.com
    Source: www.madeiradata.com
    Disclaimer:
    The author is not responsible for any damage this
    script or any of its variations may cause.
    @@ -24,7 +24,7 @@ Description:
    Instructions:
    There's no need to change anything except the values in
    the Configuration Area at the top of this script.
    the Configuration Area at the beginning of this script.
    Any other changes could change the behaviour of this script
    and will be done at your own risk.
    In any case, this script is commented as much as possible
    40 changes: 27 additions & 13 deletions Generate_Merge_For_Table.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -3,8 +3,8 @@
    Generate MERGE Statement for a Table
    ===================================================
    Copyright: Eitan Blumin (C) 2012
    Email: eitan@madeira.co.il
    Source: www.madeira.co.il
    Email: eitan@madeiradata.com
    Source: www.madeiradata.com
    Disclaimer:
    The author is not responsible for any damage this
    script or any of its variations may cause.
    @@ -47,11 +47,13 @@ GO
    Sample usage:
    EXEC usp_Generate_Merge_For_Table 'tbl_forms', 'dbo'
    */
    CREATE PROCEDURE usp_Generate_Merge_For_Table
    CREATE PROCEDURE [dbo].[usp_Generate_Merge_For_Table]
    @CurrTable SYSNAME, -- table name
    @CurrSchema SYSNAME = 'dbo', -- table schema name

    @delete_unmatched_rows BIT = 1, -- enable/disable DELETION of rows
    @update_existing_rows BIT = 1, -- enable/disable UPDATE of rows
    @insert_new_rows BIT = 1, -- enable/disable INSERT of rows
    @debug_mode BIT = 0, -- enable/disable debug mode
    @include_timestamp BIT = 0, -- include timestamp columns or not
    @ommit_computed_cols BIT = 1, -- ommit computed columns or not (in case target table doesn't have computed columns)
    @@ -87,6 +89,13 @@ SELECT
    @Values = NULL,
    @HasIdentity = 0

    -- Make sure table exists
    IF OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable)) IS NULL
    BEGIN
    RAISERROR(N'ERROR: Table [%s].[%s] not found.', 16, 1, @CurrSchema, @CurrTable) WITH NOWAIT;
    GOTO Quit;
    END

    -- find the table's Primary Key column(s) to build a JOIN clause
    SELECT
    @PKJoinClause = ISNULL(@PKJoinClause + N'
    @@ -118,7 +127,7 @@ BEGIN
    GOTO Quit;
    END

    -- get the first column ID
    -- start with the first column
    SELECT
    @CurrColumnId = MIN(ORDINAL_POSITION)
    FROM
    @@ -144,7 +153,7 @@ BEGIN
    IF @debug_mode = 1
    PRINT 'Processing column ' + @CurrColumnName

    -- Making sure whether to output computed columns or not
    -- Choosing whether to output computed columns or not
    IF @ommit_computed_cols = 1
    BEGIN
    IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + @CurrTable),SUBSTRING(@CurrColumnName,2,LEN(@CurrColumnName) - 2),'IsComputed')) = 1
    @@ -243,7 +252,7 @@ IF @debug_mode = 1
    -- Execute command and get the @Values parameter as output
    EXECUTE sp_executesql @CMD, @Params, @Values OUTPUT

    -- If table returned no rows
    -- If table returned no rows, then there's nothing to output other than deletion command
    IF @@ROWCOUNT = 0 OR @Values IS NULL
    BEGIN
    -- If deletion is enabled
    @@ -272,16 +281,21 @@ USING (VALUES
    ' + @Values + N'
    ) AS src(' + @ColumnList + N')
    ON
    ' + @PKJoinClause + N'
    ' + @PKJoinClause
    + CASE WHEN @update_existing_rows = 1 THEN N'
    WHEN MATCHED THEN
    UPDATE SET
    ' + @UpdateSet + N'
    ' + @UpdateSet
    ELSE N'' END
    + CASE WHEN @insert_new_rows = 1 THEN N'
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (' + @ColumnList + N')
    VALUES (' + @ColumnList + N')
    ' + CASE WHEN @delete_unmatched_rows = 1 THEN -- optional
    N'WHEN NOT MATCHED BY SOURCE THEN
    DELETE' ELSE N'' END + N'
    VALUES (' + @ColumnList + N')'
    ELSE N'' END
    + CASE WHEN @delete_unmatched_rows = 1 THEN N'
    WHEN NOT MATCHED BY SOURCE THEN
    DELETE'
    ELSE N'' END + N'
    ;
    ';

    @@ -328,4 +342,4 @@ BEGIN
    END
    END

    SELECT @MergeStmnt AS Command;
    SELECT @MergeStmnt AS Command;
  7. EitanBlumin created this gist May 7, 2018.
    204 changes: 204 additions & 0 deletions Generate_Merge_For_All_Tables.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,204 @@
    /*
    ===================================================
    Generate MERGE Statements for All Tables
    ===================================================
    Copyright: Eitan Blumin (C) 2012
    Email: [email protected]
    Source: www.madeira.co.il
    Disclaimer:
    The author is not responsible for any damage this
    script or any of its variations may cause.
    Do not execute it or any variations of it on production
    environments without first verifying its validity
    on controlled testing and/or QA environments.
    You may use this script at your own risk and may change it
    to your liking, as long as you leave this disclaimer header
    fully intact and unchanged.
    Description:
    This script finds all the tables in the database (only such with a primary key)
    and generates a .sql file for each table, which contains a MERGE statement
    using all the records currently present in the table.
    Upon execution of these generated scripts, they will "initialize" the table
    to only contain the records that were present at the time of the script generation.
    Instructions:
    There's no need to change anything except the values in
    the Configuration Area at the top of this script.
    Any other changes could change the behaviour of this script
    and will be done at your own risk.
    In any case, this script is commented as much as possible
    to help you understand how it works.
    Known Issues:
    Due to the use of MERGE statements and VALUES constructor, these scripts will only
    work in SQL Server version 2008 or higher.
    If a MERGE statement is executed for a table which tries to delete rows, and these rows
    are referenced by another table using a foreign key WITHOUT ON DELETE CASCADE,
    then the execution will fail. For these scripts to work properly, all foreign keys
    must have ON DELETE CASCADE turned on.
    If at the time of the MERGE script generation, any tables have no rows,
    The script file contents for them will be a single DELETE statement which deletes all records.
    Columns with the IMAGE data type may cause conversion errors.
    */

    ---------------- Start of Configuration Area ----------------

    DECLARE
    -- Target path where to save your .sql files (must be ended with a slash \):
    @OutputFolderPath NVARCHAR(4000) = N'C:\Temp\Output\',

    -- BCP template for executing the MERGE statement generation procedure:
    -- Change this to connect to the desired server using a working username and password (or use -T for trusted connection)
    -- For more information on BCP please visit: http://msdn.microsoft.com/en-us/library/ms162802.aspx
    /* This "template" uses placeholders for several changing variables (later replaced with values using REPLACE).
    Each of these placeholders must not be removed or changed from this template:
    {CurrTable}
    {CurrSchema}
    {FileName}
    */
    @BCPTemplate NVARCHAR(4000) = N'bcp "EXEC EitanTest.dbo.usp_Generate_Merge_For_Table ''{CurrTable}'', ''{CurrSchema}''" queryout "{FileName}" -c -T -S .'


    ---------------- End of Configuration Area ----------------
    -------- Changes below this line are at your own risk --------

    SET NOCOUNT ON;

    -- Variable declaration
    DECLARE
    @CurrSchema SYSNAME,
    @CurrTable SYSNAME,
    @CurrMergeStmnt NVARCHAR(MAX),
    @FileName NVARCHAR(4000),
    @CMD NVARCHAR(4000)

    -- Initialization of a cursor to traverse all the tables in the database
    -- The tables are selected using a hierarchical query to conform with correct order
    -- that the data should be inserted due to foreign key constraints.
    -- (parent table will be generated before child table)
    DECLARE ProcessTables CURSOR FOR
    WITH reftree
    AS
    (
    SELECT
    ObjectId = ReferencingTables.object_id ,
    SchemaName = OBJECT_SCHEMA_NAME(ReferencingTables.object_id) ,
    TableName = ReferencingTables.name ,
    Depth = 1
    FROM
    sys.tables AS ReferencingTables
    LEFT OUTER JOIN
    sys.foreign_keys AS ForeignKeys
    ON
    ReferencingTables.object_id = ForeignKeys.parent_object_id
    AND
    ReferencingTables.object_id != ForeignKeys.referenced_object_id
    WHERE
    ForeignKeys.object_id IS NULL
    AND
    ReferencingTables.is_ms_shipped = 0
    -- Only get tables with a primary key
    AND EXISTS (
    SELECT NULL
    FROM sys.indexes AS ind
    WHERE
    ind.object_id = ReferencingTables.object_id
    AND ind.is_primary_key = 1
    )

    UNION ALL

    SELECT
    ObjectId = ReferencingTables.object_id ,
    SchemaName = OBJECT_SCHEMA_NAME(ReferencingTables.object_id) ,
    TableName = ReferencingTables.name ,
    Depth = TableHierarchy.Depth + 1
    FROM
    sys.tables AS ReferencingTables
    INNER JOIN
    sys.foreign_keys AS ForeignKeys
    ON
    ReferencingTables.object_id = ForeignKeys.parent_object_id
    AND
    ReferencingTables.object_id != ForeignKeys.referenced_object_id
    INNER JOIN
    reftree AS TableHierarchy
    ON
    ForeignKeys.referenced_object_id = TableHierarchy.ObjectId
    WHERE
    -- Only get tables with a primary key
    EXISTS (
    SELECT NULL
    FROM sys.indexes AS ind
    WHERE
    ind.object_id = ReferencingTables.object_id
    AND ind.is_primary_key = 1
    )
    )
    SELECT
    SchemaName, TableName
    FROM reftree
    GROUP BY
    ObjectId, SchemaName, TableName
    ORDER BY
    MAX(Depth) ASC
    ;

    -- Open cursor
    OPEN ProcessTables

    -- Fetch first row from cursor
    FETCH NEXT FROM ProcessTables INTO @CurrSchema, @CurrTable

    -- While a row is returned from the cursor
    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- Display a message with the current table name
    PRINT ''
    RAISERROR(N'-------------- %s --------------', 0, 1, @CurrTable) WITH NOWAIT;
    PRINT ''

    -- Initialize full file path based on table and schema names
    SET @FileName = @OutputFolderPath + @CurrSchema + '.' + @CurrTable + '.sql'

    -- Replace placeholders with current settings
    SET @CMD = REPLACE(@BCPTemplate, '{CurrTable}',REPLACE(@CurrTable,'''',''''''))
    SET @CMD = REPLACE(@CMD, '{CurrSchema}', REPLACE(@CurrSchema,'''',''''''))
    SET @CMD = REPLACE(@CMD, '{FileName}', @FileName)

    PRINT 'Saving to ' + @FileName
    PRINT @CMD

    DECLARE @Output AS TABLE(Msg NVARCHAR(MAX));
    DECLARE @Msg NVARCHAR(MAX);

    -- Execute the BCP command using CMDSHELL and save output in a variable table
    INSERT INTO @Output
    EXEC xp_cmdshell @CMD

    -- Concatenate the output from the table into a string variable and print it
    SELECT
    -- This will concatenate all messages with a new line (char(10)) between them.
    @Msg = ISNULL(@Msg + CHAR(10),'') + Msg
    FROM
    @Output
    WHERE
    Msg IS NOT NULL

    RAISERROR(@Msg,0,1) WITH NOWAIT;

    -- Reset output table and variable
    DELETE @Output;
    SET @Msg = NULL;

    -- Fetch the next row from cursor
    FETCH NEXT FROM ProcessTables INTO @CurrSchema, @CurrTable
    END

    -- Close and destroy the cursor object
    CLOSE ProcessTables
    DEALLOCATE ProcessTables
    331 changes: 331 additions & 0 deletions Generate_Merge_For_Table.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,331 @@
    /*
    ===================================================
    Generate MERGE Statement for a Table
    ===================================================
    Copyright: Eitan Blumin (C) 2012
    Email: [email protected]
    Source: www.madeira.co.il
    Disclaimer:
    The author is not responsible for any damage this
    script or any of its variations may cause.
    Do not execute it or any variations of it on production
    environments without first verifying its validity
    on controlled testing and/or QA environments.
    You may use this script at your own risk and may change it
    to your liking, as long as you leave this disclaimer header
    fully intact and unchanged.
    Description:
    This procedure generates a MERGE statement for the table provided as a parameter,
    using the records currently in the table.
    The MERGE statement is returned in a query as a single row and column.
    If there are no rows in the table, a simple DELETE statement will be generated instead.
    Instructions:
    The resulting script from this procedure can be used to "initialize" or "reset"
    the desired table to the state it was in during the script generation.
    For example: You're about to perform tests on your table and you wish to have
    a "starting point" to which you can reset the table at any time.
    Before starting your tests, you run this procedure to generate the initialization
    script for the table and save it somewhere. You do your tests, and at any time
    you can execute the script you saved to reset the table back to its state
    at the beginning of the tests (of course, strictly in terms of data - not structure).
    Important Note:
    Tables that don't have a primary key are not supported!
    */

    IF OBJECT_ID('usp_Generate_Merge_For_Table', 'P') IS NOT NULL
    BEGIN
    PRINT 'Dropping existing procedure...'
    DROP PROCEDURE usp_Generate_Merge_For_Table
    END

    PRINT 'Creating procedure...'
    GO
    /*
    Sample usage:
    EXEC usp_Generate_Merge_For_Table 'tbl_forms', 'dbo'
    */
    CREATE PROCEDURE usp_Generate_Merge_For_Table
    @CurrTable SYSNAME, -- table name
    @CurrSchema SYSNAME = 'dbo', -- table schema name

    @delete_unmatched_rows BIT = 1, -- enable/disable DELETION of rows
    @debug_mode BIT = 0, -- enable/disable debug mode
    @include_timestamp BIT = 0, -- include timestamp columns or not
    @ommit_computed_cols BIT = 1, -- ommit computed columns or not (in case target table doesn't have computed columns)
    @top_clause NVARCHAR(4000) = N'TOP 100 PERCENT' -- you can use this to limit number of generate rows (e.g. TOP 200)
    AS

    SET NOCOUNT ON;

    -- Variable declaration
    DECLARE
    @MergeStmnt NVARCHAR(MAX),

    @CurrColumnId INT,
    @CurrColumnName SYSNAME,
    @CurrColumnType VARCHAR(1000),
    @ColumnList NVARCHAR(MAX),
    @UpdateSet NVARCHAR(MAX),
    @PKJoinClause NVARCHAR(MAX),
    @HasIdentity BIT,
    @GetValues NVARCHAR(MAX),
    @Values NVARCHAR(MAX)

    -- Init variables
    SELECT
    @CurrColumnId = NULL,
    @CurrColumnName = NULL,
    @CurrColumnType = NULL,
    @MergeStmnt = NULL,
    @ColumnList = NULL,
    @UpdateSet = NULL,
    @PKJoinClause = NULL,
    @GetValues = NULL,
    @Values = NULL,
    @HasIdentity = 0

    -- find the table's Primary Key column(s) to build a JOIN clause
    SELECT
    @PKJoinClause = ISNULL(@PKJoinClause + N'
    AND ',N'') + 'trgt.' + QUOTENAME(col.name) + N' = src.' + QUOTENAME(col.name)
    FROM
    sys.indexes AS ind
    INNER JOIN
    sys.index_columns AS indcol
    ON
    ind.object_id = indcol.object_id
    AND ind.index_id = indcol.index_id
    INNER JOIN
    sys.columns AS col
    ON
    ind.object_id = col.object_id
    AND indcol.column_id = col.column_id
    WHERE
    ind.is_primary_key = 1
    AND ind.object_id = OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable))

    IF @debug_mode = 1
    PRINT 'PK Join Clause:
    ' + @PKJoinClause

    -- If nothing found, abort (table is not supported)
    IF @PKJoinClause IS NULL
    BEGIN
    RAISERROR(N'ERROR: Table %s is not supported because it''s missing a Primary Key.', 16, 1, @CurrTable) WITH NOWAIT;
    GOTO Quit;
    END

    -- get the first column ID
    SELECT
    @CurrColumnId = MIN(ORDINAL_POSITION)
    FROM
    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
    WHERE
    TABLE_NAME = @CurrTable
    AND TABLE_SCHEMA = @CurrSchema


    -- loop through all the table columns, to get the column names and their data types
    WHILE @CurrColumnId IS NOT NULL
    BEGIN
    SELECT
    @CurrColumnName = QUOTENAME(COLUMN_NAME),
    @CurrColumnType = DATA_TYPE
    FROM
    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
    WHERE
    ORDINAL_POSITION = @CurrColumnId
    AND TABLE_NAME = @CurrTable
    AND TABLE_SCHEMA = @CurrSchema

    IF @debug_mode = 1
    PRINT 'Processing column ' + @CurrColumnName

    -- Making sure whether to output computed columns or not
    IF @ommit_computed_cols = 1
    BEGIN
    IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + @CurrTable),SUBSTRING(@CurrColumnName,2,LEN(@CurrColumnName) - 2),'IsComputed')) = 1
    BEGIN
    GOTO SKIP_COLUMN
    END
    END

    -- Concatenate column value selection to the values list
    SET @GetValues = ISNULL( @GetValues + ' + '',''' , '''(''' ) + ' + ' +
    CASE
    -- Format column value retrieval based on its data type
    WHEN @CurrColumnType IN ('text','char','varchar')
    THEN
    'COALESCE('''''''' + REPLACE(CONVERT(nvarchar(max),' + @CurrColumnName + '),'''''''','''''''''''')+'''''''',''NULL'')'
    WHEN @CurrColumnType IN ('ntext','nchar','nvarchar','xml')
    THEN
    'COALESCE(''N'''''' + REPLACE(CONVERT(nvarchar(max),' + @CurrColumnName + '),'''''''','''''''''''')+'''''''',''NULL'')'
    WHEN @CurrColumnType LIKE '%date%'
    THEN
    'COALESCE('''''''' + RTRIM(CONVERT(varchar(max),' + @CurrColumnName + ',109))+'''''''',''NULL'')'
    WHEN @CurrColumnType IN ('uniqueidentifier')
    THEN
    'COALESCE('''''''' + REPLACE(CONVERT(varchar(255),RTRIM(' + @CurrColumnName + ')),'''''''','''''''''''')+'''''''',''NULL'')'
    WHEN @CurrColumnType IN ('binary','varbinary','image')
    THEN
    'COALESCE(RTRIM(CONVERT(nvarchar(max),' + @CurrColumnName + ',1)),''NULL'')'
    WHEN @CurrColumnType IN ('timestamp','rowversion')
    THEN
    CASE
    WHEN @include_timestamp = 0
    THEN
    '''DEFAULT'''
    ELSE
    'COALESCE(RTRIM(CONVERT(varchar(max),' + 'CONVERT(int,' + @CurrColumnName + '))),''NULL'')'
    END
    WHEN @CurrColumnType IN ('float','real','money','smallmoney')
    THEN
    'COALESCE(LTRIM(RTRIM(' + 'CONVERT(varchar(max), ' + @CurrColumnName + ',2)' + ')),''NULL'')'
    ELSE
    'COALESCE(LTRIM(RTRIM(' + 'CONVERT(varchar(max), ' + @CurrColumnName + ')' + ')),''NULL'')'
    END

    -- Concatenate column name to column list
    SET @ColumnList = ISNULL(@ColumnList + N',',N'') + @CurrColumnName

    -- Make sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
    IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + @CurrTable),SUBSTRING(@CurrColumnName,2,LEN(@CurrColumnName) - 2),'IsIdentity')) = 1
    BEGIN
    SET @HasIdentity = 1
    END
    ELSE
    BEGIN
    -- If column is not IDENTITY, concatenate it to UPDATE SET clause
    SET @UpdateSet = ISNULL(@UpdateSet + N'
    , ', N'') + @CurrColumnName + N' = src.' + @CurrColumnName
    END

    SKIP_COLUMN: -- The label used in GOTO to skip column

    -- Get next column in order
    SELECT
    @CurrColumnId = MIN(ORDINAL_POSITION)
    FROM
    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
    WHERE
    ORDINAL_POSITION > @CurrColumnId
    AND TABLE_NAME = @CurrTable
    AND TABLE_SCHEMA = @CurrSchema


    -- Column loop ends here
    END

    -- Finalize VALUES constructor
    SET @GetValues = @GetValues + ' + '')'' ';

    IF @debug_mode = 1
    PRINT 'Values Retrieval:
    ' + @GetValues + '
    ';

    -- Using everything we found above, save all the table records as a values constructor (using dynamic SQL)
    DECLARE @Params NVARCHAR(MAX)
    DECLARE @CMD NVARCHAR(MAX);

    SET @Params = N'@Result NVARCHAR(MAX) OUTPUT'
    SET @CMD = 'SELECT ' + @top_clause + N'
    @Result = ISNULL(@Result + '',
    '','''') + ' + @GetValues + ' FROM ' + QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable)

    IF @debug_mode = 1
    SELECT @CMD;

    -- Execute command and get the @Values parameter as output
    EXECUTE sp_executesql @CMD, @Params, @Values OUTPUT

    -- If table returned no rows
    IF @@ROWCOUNT = 0 OR @Values IS NULL
    BEGIN
    -- If deletion is enabled
    IF @delete_unmatched_rows = 1
    -- Generate a simple DELETE statement
    SET @MergeStmnt = N'DELETE FROM ' + QUOTENAME(@CurrSchema) + N'.' + QUOTENAME(@CurrTable)
    ELSE
    -- Otherwise, generate an empty script
    SET @MergeStmnt = N''
    END
    ELSE
    -- Otherwise, build the MERGE statement
    BEGIN

    -- Use IDENTITY_INSERT if table has an identity column
    IF @HasIdentity = 1
    SET @MergeStmnt = 'SET IDENTITY_INSERT ' + QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable) + ' ON;'
    ELSE
    SET @MergeStmnt = N''

    -- Build the MERGE statement using all the parts we found
    SET @MergeStmnt = @MergeStmnt + N'
    MERGE INTO ' + QUOTENAME(@CurrSchema) + N'.' + QUOTENAME(@CurrTable) + N' AS trgt
    USING (VALUES
    ' + @Values + N'
    ) AS src(' + @ColumnList + N')
    ON
    ' + @PKJoinClause + N'
    WHEN MATCHED THEN
    UPDATE SET
    ' + @UpdateSet + N'
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (' + @ColumnList + N')
    VALUES (' + @ColumnList + N')
    ' + CASE WHEN @delete_unmatched_rows = 1 THEN -- optional
    N'WHEN NOT MATCHED BY SOURCE THEN
    DELETE' ELSE N'' END + N'
    ;
    ';

    -- Use IDENTITY_INSERT if table has an identity column
    IF @HasIdentity = 1
    SET @MergeStmnt = @MergeStmnt + 'SET IDENTITY_INSERT ' + QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable) + ' OFF;'

    END

    Quit:

    -- Output the final statement

    -- Print long text
    DECLARE
    @CurrStart int,
    @TotalLen int,
    @CurrID int,
    @CurrMsg nvarchar(max)

    SELECT
    @CurrStart = 1,
    @TotalLen = LEN(@MergeStmnt);

    WHILE @CurrStart < @TotalLen
    BEGIN
    -- Find next linebreak
    SET @CurrID = CHARINDEX(CHAR(10),@MergeStmnt,@CurrStart)

    -- If linebreak found
    IF @CurrID > 0
    BEGIN
    -- Trim line from message, print it and increase index
    SET @CurrMsg = SUBSTRING(@MergeStmnt,@CurrStart,@CurrID-@CurrStart-1)
    PRINT @CurrMsg
    SET @CurrStart = @CurrID + 1
    END
    ELSE
    BEGIN
    -- Print last line
    SET @CurrMsg = SUBSTRING(@MergeStmnt,@CurrStart,@TotalLen)
    PRINT @CurrMsg
    SET @CurrStart = @TotalLen
    END
    END

    SELECT @MergeStmnt AS Command;