SQL Server最小可复现示例脚本宽表执行时STRING_AGG返回NULL修复建议

超宽表生成VALUES时STRING_AGG返回NULL修复方案 #

问题根因 #

• 超宽表列数较多时，FOR XML PATH拼接列逻辑的结果默认被截断为NVARCHAR(4000)，超过长度后直接返回NULL，导致后续动态SQL语法错误。
• 原脚本STRING_AGG调用中CAST部分的单引号转义冗余，生成的动态SQL存在语法错误，执行失败返回NULL。
• 拼接过程中字符串常量默认为长度受限类型，总长度超过4000时会触发隐式转换失败，返回NULL。

具体修复点 #

• 所有使用FOR XML PATH拼接的部分，强制指定返回NVARCHAR(MAX)类型，彻底避免长字符串截断。
• 修正STRING_AGG的转义逻辑，移除冗余单引号，同时显式给每行的列值包裹括号，添加WITHIN GROUP保证行顺序符合原表采样逻辑。
• 所有动态拼接的字符串片段，显式转换为NVARCHAR(MAX)，避免隐式短类型转换。
• 替换PRINT为结果集输出：PRINT本身有8000字符的输出限制，超宽表生成的语句过长时会被截断，直接将生成的语句作为结果集输出，方便完整复制。

修改后的完整脚本 #

/* ---------------------------------------
-- 基于原表生成最小可复现示例脚本
-- 自动生成以下语句：
--    -- 与原表结构匹配的临时表DROP、CREATE语句
--    -- 基于原表采样数据的INSERT语句
--  替换PRINT为结果集输出，避免长语句被截断
*/ ---------------------------------------

DECLARE @tableName NVARCHAR(MAX) = 'testTable', -- 原表名填在这里
        @recordCount INT = 5,                   -- 要采样的示例记录数
        @buildStmt NVARCHAR(MAX),
        @insertStmt NVARCHAR(MAX),
        @valuesStmt NVARCHAR(MAX),
        @insertCol NVARCHAR(MAX),
        @strAgg NVARCHAR(MAX)

IF (EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tableName))
BEGIN

    -- 生成临时表的DROP、CREATE语句
    SET @buildStmt = N'IF OBJECT_ID(''tempdb..#' + @tableName + N''') IS NOT NULL DROP TABLE #' + @tableName + CHAR(10) + CHAR(10) +
               N'CREATE TABLE #' + @tableName + N' (' + CHAR(10)

    SELECT @buildStmt = @buildStmt + N' ' + C.[Name] + N' ' +
                T.[Name] +
                CASE WHEN T.[Name] IN ('varchar','char','nchar','nvarchar') 
                    THEN N'(' + CAST(C.[Length] AS NVARCHAR(MAX)) + N') ' 
                    ELSE N' ' 
                END +
                N'NULL,' + CHAR(10)
    FROM sysobjects O
    JOIN syscolumns C ON C.id = O.id
    JOIN systypes T ON T.xusertype = C.xusertype
    WHERE O.[name] = @TableName
    ORDER BY C.ColID

    SET @buildStmt = SUBSTRING(@buildStmt,1,LEN(@buildStmt) - 2) + CHAR(10) + N')' + CHAR(10)

    -- 生成INSERT INTO列列表
    SELECT @insertStmt =  N'INSERT INTO #' + @tableName + N' (' +
        STUFF ((
            SELECT N', [' + C.[Name] + N']'
            FROM sysobjects O
            JOIN syscolumns C ON C.id = O.id
            WHERE O.[name] = @TableName
            ORDER BY C.ColID
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, N'') 
        +N')' 

    -- 生成VALUES的列值拼接逻辑
    SELECT @insertCol = STUFF ((
            SELECT N'''''''+'CONVERT(NVARCHAR(MAX),' +
            N'[' + C.[Name] + N']' + 
            N')+'''''',''+' 
            FROM sysobjects O
            JOIN syscolumns C ON C.id = O.id
            JOIN systypes T ON T.xusertype = C.xusertype
            WHERE O.[name] = @TableName
            ORDER BY C.ColID
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, N'')
    SET @insertCol = SUBSTRING(@insertCol,1,LEN(@insertCol) - 1) 

    -- 动态生成VALUES行并聚合
    SELECT @strAgg = N';WITH CTE AS (SELECT TOP(' + CONVERT(NVARCHAR(MAX),@recordCount) + N') * FROM ' + QUOTENAME(@tableName) + N') ' +
                     N' SELECT @valuesStmt = STRING_AGG(CAST(''('' + (' + @insertCol + N') + '')'' AS NVARCHAR(MAX)), ''),' + CHAR(10) + N'(''') WITHIN GROUP (ORDER BY (SELECT NULL))' +
                     N' FROM CTE' 
    EXEC sp_executesql @strAgg,N'@valuesStmt NVARCHAR(MAX) OUTPUT', @valuesStmt OUTPUT

    -- 输出所有生成的语句到结果集，避免PRINT长度限制
    SELECT @buildStmt AS GenerateStmt
    UNION ALL
    SELECT @insertStmt + CHAR(10) + N'VALUES ' + @valuesStmt AS GenerateStmt

END
ELSE
BEGIN
    SELECT N'目标表【' + @tableName + N'】不存在' AS GenerateStmt
END

内容的提问来源于stack exchange，提问作者WAMLeslie