Create test data in SQL Server

Question

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.

Answer 1

There is a program from red gate software which will do this for you. It's called SQL Data Generator.

Answer 2

Some flavours of Visual Studio have data generation built in. If you use database projects in it you can create data generation plans. Here's the MSDN article

Answer 3

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

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.

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.