SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;

DECLARE @table NVARCHAR(255) = 'dbo.YourTableName';
DECLARE @where NVARCHAR(MAX) = '1=1';
DECLARE @includeBinaryColumns BIT = 0;
DECLARE @includeDelete BIT = 1;
DECLARE @excludeColumns TABLE (ColumnName NVARCHAR(255));
-- INSERT INTO @excludeColumns (ColumnName) VALUES ('PasswordHash'), ('SecurityStamp');

DECLARE @ChunkSize INT = 50000;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @RowCount INT = 0;

BEGIN TRY
    -- Merge statement format should be:
    -- MERGE <target_table> AS target
    -- USING (VALUES (<row_expression>), (<row_expression>)) AS source
    -- ON (<pk_where_clause>)
    -- WHEN MATCHED THEN UPDATE SET <update_clause>
    -- WHEN NOT MATCHED THEN INSERT (<column_list>) VALUES (<insert_values_clause>);

    -- Get primary key columns
    DECLARE @PKColumns TABLE (
        ColumnName NVARCHAR(255),
        OrdinalPosition INT,
        DataType NVARCHAR(255)
    );

    INSERT INTO @PKColumns (ColumnName, OrdinalPosition, DataType)
    SELECT
        kcu.COLUMN_NAME,
        kcu.ORDINAL_POSITION,
        c.DATA_TYPE
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
        ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
        AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
        AND tc.TABLE_NAME = kcu.TABLE_NAME
    INNER JOIN INFORMATION_SCHEMA.COLUMNS c
        ON kcu.TABLE_SCHEMA = c.TABLE_SCHEMA
        AND kcu.TABLE_NAME = c.TABLE_NAME
        AND kcu.COLUMN_NAME = c.COLUMN_NAME
    WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
        AND tc.TABLE_SCHEMA = (CASE WHEN CHARINDEX('.', @table) > 0 THEN LEFT(@table, CHARINDEX('.', @table) - 1) ELSE 'dbo' END)
        AND tc.TABLE_NAME = (CASE WHEN CHARINDEX('.', @table) > 0 THEN SUBSTRING(@table, CHARINDEX('.', @table) + 1, LEN(@table)) ELSE @table END)
    ORDER BY kcu.ORDINAL_POSITION;

    IF NOT EXISTS (SELECT 1 FROM @PKColumns)
    BEGIN
        SET @ErrorMessage = 'Table ' + @table + ' does not have a primary key.';
        RAISERROR(@ErrorMessage, 16, 1);
        RETURN;
    END

    -- Get all columns
    DECLARE @Columns TABLE (
        ColumnName NVARCHAR(255),
        DataType NVARCHAR(255),
        IsNullable BIT,
        OrdinalPosition INT,
        IsBinary BIT,
        ColumnDefault NVARCHAR(MAX),
        CharacterMaxLength INT,
        NumericPrecision INT,
        NumericScale INT
    );

    INSERT INTO @Columns (ColumnName, DataType, IsNullable, OrdinalPosition, IsBinary, ColumnDefault, CharacterMaxLength, NumericPrecision, NumericScale)
    SELECT
        c.COLUMN_NAME,
        c.DATA_TYPE,
        CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,
        c.ORDINAL_POSITION,
        CASE
            WHEN c.DATA_TYPE IN ('image', 'varbinary', 'binary') OR
                 (c.DATA_TYPE = 'varbinary' AND c.CHARACTER_MAXIMUM_LENGTH = -1) OR
                 (c.DATA_TYPE = 'binary' AND c.CHARACTER_MAXIMUM_LENGTH = -1)
            THEN 1
            ELSE 0
        END,
        c.COLUMN_DEFAULT,
        c.CHARACTER_MAXIMUM_LENGTH,
        c.NUMERIC_PRECISION,
        c.NUMERIC_SCALE
    FROM INFORMATION_SCHEMA.COLUMNS c
    WHERE c.TABLE_SCHEMA = (CASE WHEN CHARINDEX('.', @table) > 0 THEN LEFT(@table, CHARINDEX('.', @table) - 1) ELSE 'dbo' END)
        AND c.TABLE_NAME = (CASE WHEN CHARINDEX('.', @table) > 0 THEN SUBSTRING(@table, CHARINDEX('.', @table) + 1, LEN(@table)) ELSE @table END)
    ORDER BY c.ORDINAL_POSITION;

    -- Filter columns based on binary handling
    DECLARE @SelectedColumns TABLE (
        ColumnName NVARCHAR(255),
        DataType NVARCHAR(255),
        IsNullable BIT,
        OrdinalPosition INT,
        ColumnDefault NVARCHAR(MAX),
        CharacterMaxLength INT,
        NumericPrecision INT,
        NumericScale INT
    );

    INSERT INTO @SelectedColumns (ColumnName, DataType, IsNullable, OrdinalPosition, ColumnDefault, CharacterMaxLength, NumericPrecision, NumericScale)
    SELECT c.ColumnName, c.DataType, c.IsNullable, c.OrdinalPosition, c.ColumnDefault, c.CharacterMaxLength, c.NumericPrecision, c.NumericScale
    FROM @Columns c
    WHERE (@includeBinaryColumns = 1 OR c.IsBinary = 0)
        AND NOT EXISTS (SELECT 1 FROM @excludeColumns e WHERE e.ColumnName = c.ColumnName);

    -- Build column lists
    DECLARE @ColumnList NVARCHAR(MAX);
    DECLARE @PKWhereClause NVARCHAR(MAX);
    DECLARE @PKSelectClause NVARCHAR(MAX);
    DECLARE @UpdateClause NVARCHAR(MAX);
    DECLARE @InsertValuesClause NVARCHAR(MAX);

    SELECT @ColumnList = STUFF((
        SELECT ', ' + QUOTENAME(ColumnName)
        FROM @SelectedColumns
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

    SELECT @PKWhereClause = STUFF((
        SELECT ' AND ' + 'target.' + QUOTENAME(pk.ColumnName) + ' = source.' + QUOTENAME(pk.ColumnName)
        FROM @PKColumns pk
        WHERE EXISTS (SELECT 1 FROM @SelectedColumns s WHERE s.ColumnName = pk.ColumnName)
        ORDER BY pk.OrdinalPosition
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 5, '');

    SELECT @PKSelectClause = STUFF((
        SELECT ', ' + QUOTENAME(pk.ColumnName)
        FROM @PKColumns pk
        WHERE EXISTS (SELECT 1 FROM @SelectedColumns s WHERE s.ColumnName = pk.ColumnName)
        ORDER BY pk.OrdinalPosition
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

    IF @PKWhereClause IS NULL OR LTRIM(RTRIM(@PKWhereClause)) = ''
    BEGIN
        SET @ErrorMessage = 'At least one primary key column must be included (cannot exclude all PK columns).';
        RAISERROR(@ErrorMessage, 16, 1);
        RETURN;
    END

    SELECT @UpdateClause = STUFF((
        SELECT ', ' + QUOTENAME(ColumnName) + ' = source.' + QUOTENAME(ColumnName)
        FROM @SelectedColumns
        WHERE ColumnName NOT IN (SELECT ColumnName FROM @PKColumns)
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

    IF @UpdateClause IS NULL OR LTRIM(RTRIM(@UpdateClause)) = ''
        SELECT @UpdateClause = (SELECT TOP 1 QUOTENAME(ColumnName) + ' = source.' + QUOTENAME(ColumnName) FROM @SelectedColumns ORDER BY OrdinalPosition);

    SELECT @InsertValuesClause = STUFF((
        SELECT ', ' + 'source.' + QUOTENAME(ColumnName)
        FROM @SelectedColumns
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

    -- Build value formatter expression per column (for literal VALUES)
    DECLARE @VarCharType NVARCHAR(20) = 'var' + 'char';
    DECLARE @CharType NVARCHAR(20) = 'c' + 'har';
    DECLARE @ValueFormats TABLE (
        OrdinalPosition INT,
        ValueExpression NVARCHAR(MAX)
    );

    INSERT INTO @ValueFormats (OrdinalPosition, ValueExpression)
    SELECT
        OrdinalPosition,
        CASE
            WHEN DataType = 'bit' THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' WHEN t.' + QUOTENAME(ColumnName) + ' = 1 THEN ''1'' ELSE ''0'' END'
            WHEN DataType = 'int' THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE CAST(t.' + QUOTENAME(ColumnName) + ' AS NVARCHAR(MAX)) END'
            WHEN DataType = 'bigint' THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE CAST(t.' + QUOTENAME(ColumnName) + ' AS NVARCHAR(MAX)) END'
            WHEN DataType = 'smallint' THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE CAST(t.' + QUOTENAME(ColumnName) + ' AS NVARCHAR(MAX)) END'
            WHEN DataType = 'tinyint' THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE CAST(t.' + QUOTENAME(ColumnName) + ' AS NVARCHAR(MAX)) END'
            WHEN DataType = 'decimal' THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE CAST(t.' + QUOTENAME(ColumnName) + ' AS NVARCHAR(MAX)) END'
            WHEN DataType = 'numeric' THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE CAST(t.' + QUOTENAME(ColumnName) + ' AS NVARCHAR(MAX)) END'
            WHEN DataType = 'float' THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE CAST(t.' + QUOTENAME(ColumnName) + ' AS NVARCHAR(MAX)) END'
            WHEN DataType = 'real' THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE CAST(t.' + QUOTENAME(ColumnName) + ' AS NVARCHAR(MAX)) END'
            WHEN DataType = 'money' THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE CAST(t.' + QUOTENAME(ColumnName) + ' AS NVARCHAR(MAX)) END'
            WHEN DataType = 'smallmoney' THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE CAST(t.' + QUOTENAME(ColumnName) + ' AS NVARCHAR(MAX)) END'
            WHEN DataType = 'date' THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE CHAR(39) + CONVERT(NVARCHAR(50), t.' + QUOTENAME(ColumnName) + ', 121) + CHAR(39) END'
            WHEN DataType = 'datetime' THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE CHAR(39) + CONVERT(NVARCHAR(50), t.' + QUOTENAME(ColumnName) + ', 121) + CHAR(39) END'
            WHEN DataType = 'datetime2' THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE CHAR(39) + CONVERT(NVARCHAR(50), t.' + QUOTENAME(ColumnName) + ', 121) + CHAR(39) END'
            WHEN DataType = 'smalldatetime' THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE CHAR(39) + CONVERT(NVARCHAR(50), t.' + QUOTENAME(ColumnName) + ', 121) + CHAR(39) END'
            WHEN DataType = 'time' THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE CHAR(39) + CONVERT(NVARCHAR(50), t.' + QUOTENAME(ColumnName) + ', 121) + CHAR(39) END'
            WHEN DataType = 'uniqueidentifier' THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE CHAR(39) + CAST(t.' + QUOTENAME(ColumnName) + ' AS NVARCHAR(36)) + CHAR(39) END'
            WHEN DataType IN ('nvarchar', 'nchar') THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE NCHAR(78) + CHAR(39) + REPLACE(REPLACE(REPLACE(REPLACE(CAST(t.' + QUOTENAME(ColumnName) + ' AS NVARCHAR(MAX)), CHAR(13), CHAR(32)), CHAR(10), CHAR(32)), CHAR(9), CHAR(32)), CHAR(39), CHAR(39)+CHAR(39)) + CHAR(39) END'
            WHEN DataType IN (@VarCharType, @CharType) THEN 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE CHAR(39) + REPLACE(REPLACE(REPLACE(REPLACE(CAST(t.' + QUOTENAME(ColumnName) + ' AS NVARCHAR(MAX)), CHAR(13), CHAR(32)), CHAR(10), CHAR(32)), CHAR(9), CHAR(32)), CHAR(39), CHAR(39)+CHAR(39)) + CHAR(39) END'
            ELSE 'CASE WHEN t.' + QUOTENAME(ColumnName) + ' IS NULL THEN ''NULL'' ELSE NCHAR(78) + CHAR(39) + REPLACE(REPLACE(REPLACE(REPLACE(CAST(t.' + QUOTENAME(ColumnName) + ' AS NVARCHAR(MAX)), CHAR(13), CHAR(32)), CHAR(10), CHAR(32)), CHAR(9), CHAR(32)), CHAR(39), CHAR(39)+CHAR(39)) + CHAR(39) END'
        END
    FROM @SelectedColumns;

    DECLARE @RowExpression NVARCHAR(MAX) = '';
    DECLARE @ValExpr NVARCHAR(MAX);
    DECLARE @First BIT = 1;
    DECLARE row_cursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT ValueExpression FROM @ValueFormats ORDER BY OrdinalPosition;
    OPEN row_cursor;
    FETCH NEXT FROM row_cursor INTO @ValExpr;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @RowExpression = @RowExpression + CASE WHEN @First = 1 THEN '' ELSE ' + '', '' + ' END + @ValExpr;
        SET @First = 0;
        FETCH NEXT FROM row_cursor INTO @ValExpr;
    END;
    CLOSE row_cursor;
    DEALLOCATE row_cursor;

    DECLARE @PKRowExpression NVARCHAR(MAX) = '';
    DECLARE pk_cursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT v.ValueExpression
        FROM @ValueFormats v
        INNER JOIN @SelectedColumns s ON v.OrdinalPosition = s.OrdinalPosition
        INNER JOIN @PKColumns pk ON pk.ColumnName = s.ColumnName
        ORDER BY pk.OrdinalPosition;
    OPEN pk_cursor;
    SET @First = 1;
    FETCH NEXT FROM pk_cursor INTO @ValExpr;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @PKRowExpression = @PKRowExpression + CASE WHEN @First = 1 THEN '' ELSE ' + '', '' + ' END + @ValExpr;
        SET @First = 0;
        FETCH NEXT FROM pk_cursor INTO @ValExpr;
    END;
    CLOSE pk_cursor;
    DEALLOCATE pk_cursor;

    -- Get row count and build MERGE with literal VALUES via dynamic SQL
    DECLARE @CountSql NVARCHAR(MAX) = 'SELECT @cnt = COUNT(*) FROM ' + @table + ' WHERE ' + @where;
    EXEC sp_executesql @CountSql, N'@cnt INT OUTPUT', @cnt = @RowCount OUTPUT;

    DECLARE @DeleteSql NVARCHAR(MAX) = NULL;

    DECLARE @ChunkOutput TABLE (
        ChunkNumber INT,
        TableName NVARCHAR(255),
        SqlStatement NVARCHAR(MAX),
        ChunkRowCount INT,
        StatementSize INT,
        StatementType NVARCHAR(50)
    );

    IF @RowCount = 0
    BEGIN
        INSERT INTO @ChunkOutput (ChunkNumber, TableName, SqlStatement, ChunkRowCount, StatementSize, StatementType)
        SELECT
            1,
            @table,
            '-- No rows to export from ' + @table + ' WHERE ' + @where,
            0,
            0,
            'MERGE';
    END
    ELSE
    BEGIN
        DECLARE @BuildSql NVARCHAR(MAX) = 'SELECT @out = STUFF((SELECT '', '' + ''('' + ' + @RowExpression + ' + '')'' FROM ' + @table + ' t WHERE ' + @where + ' FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''), 1, 2, '''')';
        DECLARE @ValuesResult NVARCHAR(MAX) = NULL;
        EXEC sp_executesql @BuildSql, N'@out NVARCHAR(MAX) OUTPUT', @out = @ValuesResult OUTPUT;

        IF @includeDelete = 1
        BEGIN
            DECLARE @BuildPKSql NVARCHAR(MAX) = 'SELECT @out = STUFF((SELECT '', '' + ''('' + ' + @PKRowExpression + ' + '')'' FROM ' + @table + ' t WHERE ' + @where + ' FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''), 1, 2, '''')';
            DECLARE @PKValuesResult NVARCHAR(MAX) = NULL;
            EXEC sp_executesql @BuildPKSql, N'@out NVARCHAR(MAX) OUTPUT', @out = @PKValuesResult OUTPUT;
            SET @DeleteSql = 'DELETE FROM ' + @table + ' WHERE ' + @where + ' AND (' + @PKSelectClause + ') NOT IN (VALUES ' + ISNULL(@PKValuesResult, '') + ')';
        END

        DECLARE @CRLF NCHAR(2) = NCHAR(13) + NCHAR(10);
        DECLARE @MergePrefix NVARCHAR(MAX) = 'MERGE ' + @table + ' AS target' + @CRLF + 'USING (VALUES ';
        DECLARE @MergeSuffix NVARCHAR(MAX) = ') AS source(' + @ColumnList + ')' + @CRLF + 'ON (' + @PKWhereClause + ')' + @CRLF + 'WHEN MATCHED THEN UPDATE SET ' + ISNULL(@UpdateClause, '') + @CRLF + 'WHEN NOT MATCHED THEN INSERT (' + @ColumnList + ') VALUES (' + @InsertValuesClause + ');';
        DECLARE @MaxValuesLen INT = @ChunkSize - LEN(@MergePrefix) - LEN(@MergeSuffix);

        IF LEN(ISNULL(@ValuesResult, '')) <= @MaxValuesLen
        BEGIN
            INSERT INTO @ChunkOutput (ChunkNumber, TableName, SqlStatement, ChunkRowCount, StatementSize, StatementType)
            SELECT
                1,
                @table,
                @MergePrefix + ISNULL(@ValuesResult, '') + @MergeSuffix,
                @RowCount,
                LEN(@MergePrefix) + LEN(ISNULL(@ValuesResult, '')) + LEN(@MergeSuffix),
                'MERGE';
        END
        ELSE
        BEGIN
            DECLARE @Remaining NVARCHAR(MAX) = @ValuesResult;
            DECLARE @ChunkNum INT = 1;
            DECLARE @ChunkValues NVARCHAR(MAX);
            DECLARE @LastDelim INT;
            DECLARE @Pos INT;
            DECLARE @RowDelim NVARCHAR(10) = ', ';
            DECLARE @FullRowDelim NVARCHAR(20) = ')' + @RowDelim + '(';

            WHILE LEN(@Remaining) > 0
            BEGIN
                IF LEN(@Remaining) <= @MaxValuesLen
                BEGIN
                    SET @ChunkValues = @Remaining;
                    SET @Remaining = '';
                END
                ELSE
                BEGIN
                    SET @LastDelim = 0;
                    SET @Pos = 1;
                    WHILE @Pos <= @MaxValuesLen
                    BEGIN
                        SET @Pos = CHARINDEX(@FullRowDelim, @Remaining, @Pos);
                        IF @Pos = 0
                            BREAK;
                        IF @Pos - 1 <= @MaxValuesLen
                            SET @LastDelim = @Pos;
                        SET @Pos = @Pos + 1;
                    END;
                    IF @LastDelim > 0
                    BEGIN
                        SET @ChunkValues = SUBSTRING(@Remaining, 1, @LastDelim);
                        SET @Remaining = SUBSTRING(@Remaining, @LastDelim + LEN(@FullRowDelim) - 1, LEN(@Remaining));
                    END
                    ELSE
                    BEGIN
                        SET @Pos = CHARINDEX(@FullRowDelim, @Remaining);
                        IF @Pos > 0
                        BEGIN
                            SET @ChunkValues = SUBSTRING(@Remaining, 1, @Pos);
                            SET @Remaining = SUBSTRING(@Remaining, @Pos + LEN(@FullRowDelim) - 1, LEN(@Remaining));
                        END
                        ELSE
                        BEGIN
                            SET @ChunkValues = @Remaining;
                            SET @Remaining = '';
                        END;
                    END;
                END;

                INSERT INTO @ChunkOutput (ChunkNumber, TableName, SqlStatement, ChunkRowCount, StatementSize, StatementType)
                SELECT
                    @ChunkNum,
                    @table,
                    @MergePrefix + @ChunkValues + @MergeSuffix,
                    (LEN(@ChunkValues) - LEN(REPLACE(@ChunkValues, @FullRowDelim, ''))) / LEN(@FullRowDelim) + 1,
                    LEN(@MergePrefix) + LEN(@ChunkValues) + LEN(@MergeSuffix),
                    'MERGE';

                SET @ChunkNum = @ChunkNum + 1;
            END;
        END;
    END;

    IF @DeleteSql IS NOT NULL
        INSERT INTO @ChunkOutput (ChunkNumber, TableName, SqlStatement, ChunkRowCount, StatementSize, StatementType)
        SELECT
            (SELECT ISNULL(MAX(ChunkNumber), 0) + 1 FROM @ChunkOutput),
            @table,
            @DeleteSql,
            0,
            LEN(@DeleteSql),
            'DELETE';

    SELECT
        ChunkNumber,
        TableName,
        SqlStatement,
        ChunkRowCount AS [RowCount],
        StatementSize,
        StatementType
    FROM @ChunkOutput
    ORDER BY ChunkNumber;

END TRY
BEGIN CATCH
    SET @ErrorMessage = ERROR_MESSAGE();
    RAISERROR(@ErrorMessage, 16, 1);
    RETURN;
END CATCH