MSSQL脚本自动生成数据

介绍

在数据库测试、开发和管理过程中，我们需要随机生成大量的测试数据和实际数据。手动创建这些数据是一个耗时、无趣和容易出错的工作。在此情况下，自动化数据生成是一个值得思考的解决方案。

在MS SQL Server中，我们可以使用T-SQL语言和其他工具（如SQL Data Generator）来生成测试数据。但是，在快速生成数据时，可能还需要一种更高效、更灵活的方法。在本文中，我们将介绍一种新的方法，使用MSSQL脚本自动生成数据。

工作原理

该方法通过使用T-SQL、动态SQL和循环语句来生成数据。脚本读取表结构的元数据，如列名、数据类型、长度、键值等，并使用这些信息生成适当的数据。例如，如果列是日期类型，则会生成随机日期；如果列是数字类型，则会生成随机数字等。

具体实现步骤

1. 创建存储过程

在开始之前，我们需要创建一个存储过程，来生成数据。以下是示例代码：

--创建生成数据的存储过程
DROP PROCEDURE IF EXISTS p_generate_data;
GO
CREATE PROCEDURE p_generate_data
AS
BEGIN
    DECLARE @table_name nvarchar(255)
    DECLARE @sql nvarchar(max)
    DECLARE @i int
    
    --获取所有表名
    DECLARE db_cursor CURSOR FOR  
    SELECT name 
    FROM sys.tables 
    
    OPEN db_cursor  
    
    FETCH NEXT FROM db_cursor INTO @table_name  
    
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
          
        --生成SQL语句
        SET @sql = N'INSERT INTO ' + @table_name + N'('  
        
        SELECT @sql = @sql + c.name + N', '  
        FROM sys.columns c  
        JOIN sys.tables t ON c.object_id = t.object_id  
        WHERE t.name = @table_name  
        AND c.column_id ! = 1  
          
        --去除sql语句中额外的逗号和空格
        SET @sql = LEFT(@sql,LEN(@sql)-1)  
        SET @sql = @sql + N') VALUES('  
        
        SELECT @sql = @sql +   
            CASE  
                WHEN c.system_type_id IN (165,167,175,231,239) --字符类型
                    THEN '''' + REPLACE(LEFT(CONVERT(varchar,newid()),(LEN(newid())/2)), '-', '') + ''''  
                WHEN c.system_type_id IN (40,42,48,52,56,59,60,62,104,106,108,122,127,165,167,173,175,231,239) --数字、货币类型
                    THEN CONVERT(nvarchar(100), ABS(CHECKSUM(NEWID())) / 100000000.0)
                WHEN c.system_type_id IN (58,61) --日期类型
                    THEN CONVERT(nvarchar(100),DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530) - 32765, GETDATE()),126)  --考虑到日期型字段不能为null，此处不支持sql server 2000以下的版本
                WHEN c.system_type_id IN (59,60) --浮点类型
                    THEN CONVERT(nvarchar(100), ABS(CHECKSUM(NEWID())) / 100000000.0, -1)  --考虑到双精度浮点型不能为null，此处不支持sql server 2000以下的版本
            END + N', '  
        FROM sys.columns c  
        JOIN sys.tables t ON c.object_id = t.object_id  
        WHERE t.name = @table_name  
        AND c.column_id ! = 1  
        
        --去除sql语句中额外的逗号和空格 
        SET @sql = LEFT(@sql,LEN(@sql)-1)  
        SET @sql = @sql + N')'  
          
        PRINT @sql  
        EXEC(@sql)
        
        FETCH NEXT FROM db_cursor INTO @table_name  
    END  
    
    CLOSE db_cursor  
    DEALLOCATE db_cursor  
END
GO

该存储过程使用游标迭代目标数据库的每个表，并生成INSERT语句插入数据。SQL语句的生成部分使用动态SQL，因此可以根据目标表的不同而动态调整。

2. 运行存储过程

现在，我们只需要运行存储过程即可生成数据。运行以下命令：

EXEC p_generate_data

该命令将生成所有表的测试数据，包括表中的所有列。数据是根据列类型自动填充的，因此生成的数据应具有合适的类型、范围和数据分布。

总结

MSSQL脚本自动化数据生成提供了一种灵活、可重复和自定义的方式来生成数据。使用T-SQL编写脚本，可以根据需要快速生成大量测试数据，并且可以方便地在不同的数据库中重复使用。此外，该方法还可以被扩展以改进数据生成器的功能，例如添加更高级的数据类型、约束和函数。

参考文章

- [Automate SQL Server Test Data Generation with PowerShell](https://www.red-gate.com/simple-talk/sql/database-administration/automate-sql-server-test-data-generation-with-powershell/)

- [SQL Server Automated Testing With PowerShell – Part 2](https://www.red-gate.com/simple-talk/sql/database-administration/sql-server-automated-testing-with-powershell-part-2/)

- [Generating Test Data in SQL Server with PowerShell](https://www.red-gate.com/simple-talk/sql/tools/generating-test-data-in-sql-server-with-powershell/)