在 SQL Server 中创建测试数据 [英] Create test data in SQL Server

问题描述

是否有人拥有或知道可以为给定表生成测试数据的 SQL 脚本?

Does anyone have or know of a SQL script that will generate test data for a given table?

理想情况下，它将查看表的架构，并根据每列的数据类型创建包含测试数据的行.

Ideally it will look at the schema of the table and create row(s) with test data based on the datatype for each column.

如果它不存在，其他人会觉得它有用吗?如果是这样，我会拔出手指写一个.

If this doesn't exist, would anyone else find it useful? If so I'll pull my finger out and write one.

推荐答案

好吧，我想我会抽出手指给自己写一个轻量级数据生成器:

Well I thought I would pull my finger out and write myself a light weight data generator:

declare @select varchar(max), @insert varchar(max), @column varchar(100),
    @type varchar(100), @identity bit, @db nvarchar(100)

set @db = N'Orders'
set @select = 'select '
set @insert = 'insert into ' + @db + ' ('


declare crD cursor fast_forward for
select column_name, data_type, 
COLUMNPROPERTY(
    OBJECT_ID(
       TABLE_SCHEMA + '.' + TABLE_NAME), 
    COLUMN_NAME, 'IsIdentity') AS COLUMN_ID
from Northwind.INFORMATION_SCHEMA.COLUMNS
where table_name = @db


open crD
fetch crD into @column, @type, @identity

while @@fetch_status = 0
begin
if @identity = 0 or @identity is null
begin
    set @insert = @insert + @column + ', ' 
    set @select = @select  + 
        case @type
            when 'int' then '1'
            when 'varchar' then '''test'''
            when 'nvarchar' then '''test'''
            when 'smalldatetime' then 'getdate()'
            when 'bit' then '0'
            else 'NULL'
        end + ', ' 
end
fetch crD into @column, @type, @identity
end 

set @select = left(@select, len(@select) - 1)
set @insert = left(@insert, len(@insert) - 1) + ')'
exec(@insert + @select)

close crD
deallocate crD

给定任何表，脚本将创建一个记录，其中包含一些任意类型的值；int、varchar、nvarchar、smalldatetime 和 bit.case 语句可以替换为一个函数.它不会向下传递依赖项，但会跳过任何种子列.

Given any table, the script will create one record with some arbitrary values for the types; int, varchar, nvarchar, smalldatetime and bit. The case statement could be replaced with a function. It won't travel down dependencies but it will skip any seeded columns.

我创建这个的动机是针对一个包含大约 50 列的表测试我的 NHibernate 映射文件，所以我很快就找到了一个可以重复使用的简单脚本.

My motivation for creating this is to test my NHibernate mapping files against a table with some 50 columns so I was after a quick a simple script which can be re-used.

这篇关于在 SQL Server 中创建测试数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！