sql - 用于从源表创建测试样本数据的 SQL 脚本

Question

我创建了下面的脚本，以便能够为其他一般问题快速创建一个最小的可重现示例。

此脚本使用原始表并生成以下PRINT语句：

• DROP和CREATE一个结构与原始表匹配的临时表
• INSERT INTO使用来自实际数据的示例的声明

我可以将原始表名以及表中所需的样本记录数添加到列出的变量中。当我运行它时，它会在 SSMS 的“消息”窗口中生成所需的所有语句。然后我可以将这些陈述复制并粘贴到我发布的问题中，这样那些回答的人就可以使用了。

我知道您可以通过“任务”>“生成脚本”在 SSMS 中获得类似的结果，但这会将事情归结为对在此处发布有用的最少代码量，而无需 SSMS 自动生成的所有不必要信息。这只是创建包含实际样本数据的简单表的复制版本的快速方法。

不幸的是，如果我在非常宽的桌子上运行它，那么一种情况不起作用。它似乎在STRING_AGG()构建VALUES. INSERT当它在宽表上运行时，它返回NULL.

有什么建议可以纠正这个问题吗？

编辑：我发现了UNIQUEIDENTIFIER列的问题并修改了下面的查询。还包括初始检查以确保该表确实存在。

/* ---------------------------------------
-- For creating minimal reproducible examples
-- based on original table and data,
-- builds the following statements
--    -- CREATE temp table with structure matching original table
--    -- INSERT statements based on actual data
--
-- Note: May not work for very wide tables due to limitations on 
--         PRINT statements
*/ ---------------------------------------

DECLARE @tableName NVARCHAR(MAX) = 'testTable', -- original table name HERE
        @recordCount INT = 5,                   -- top number of records to insert to temp table
        @buildStmt NVARCHAR(MAX),
        @insertStmt NVARCHAR(MAX),
        @valuesStmt NVARCHAR(MAX),
        @insertCol NVARCHAR(MAX),
        @strAgg NVARCHAR(MAX),
        @insertOutput NVARCHAR(MAX)

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

    -- build DROP and CREATE statements for temp table from original table
    SET @buildStmt = 'IF OBJECT_ID(''tempdb..#' + @tableName + ''') IS NOT NULL DROP TABLE #' + @tableName + CHAR(10) + CHAR(10) +
               'CREATE TABLE #' + @tableName + ' (' + CHAR(10)

    SELECT @buildStmt = @buildStmt + ' ' + C.[Name] + ' ' +
                T.[Name] +
                CASE WHEN T.[Name] IN ('varchar','varchar','char','nchar') THEN '(' + CAST(C.[Length] AS VARCHAR) + ') ' ELSE ' ' END +
                '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) + ')' + CHAR(10)
    PRINT @buildStmt

    -- build INSERT INTO statement from original table
    SELECT @insertStmt =  'INSERT INTO #' + @tableName + ' (' +
        STUFF ((
            SELECT ', [' + C.[Name] + ']'
            FROM sysobjects O
            JOIN syscolumns C ON C.id = O.id
            WHERE O.[name] = @TableName
            ORDER BY C.ColID
            FOR XML PATH('')), 1, 1, '') 
        +')' 

    PRINT @insertStmt

    -- build VALUES portion of INSERT from data in original table
    SELECT @insertCol = STUFF ((
            SELECT '''''''''+CONVERT(NVARCHAR(200),' +
            '[' + C.[Name] + ']' + 
            ')+'''''',''+' 
            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('')), 1, 1, '')
    SET @insertCol = SUBSTRING(@insertCol,1,LEN(@insertCol) - 1) 

    SELECT @strAgg = ';WITH CTE AS (SELECT TOP(' + CONVERT(VARCHAR,@recordCount) + ') * FROM ' + @tableName + ') ' +
                     ' SELECT @valuesStmt = STRING_AGG(CAST(''' + @insertCol + ' AS NVARCHAR(MAX)),''),  ('') ' +
                     ' FROM CTE' 
    EXEC sp_executesql @strAgg,N'@valuesStmt NVARCHAR(MAX) OUTPUT', @valuesStmt OUTPUT

    PRINT 'VALUES (' +REPLACE(SUBSTRING(@valuesStmt,1,LEN(@valuesStmt) - 1),',)',')') + ')'

END
ELSE
BEGIN
    PRINT 'Table does NOT exist'
END

标签： sqlsql-servertsql