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

We need step by step create query for tables need entry data. i used below codes, step by step for insert test data:

1. Create a table :

CREATE TABLE dbo.TestTableSize
(
    MyKeyField VARCHAR(10) NOT NULL,
    MyDate1 DATETIME NOT NULL,
    MyDate2 DATETIME NOT NULL,
    MyDate3 DATETIME NOT NULL,
    MyDate4 DATETIME NOT NULL,
    MyDate5 DATETIME NOT NULL
)

2. Variable Declarations

DECLARE @RowCount INT
DECLARE @RowString VARCHAR(10)
DECLARE @Random INT
DECLARE @Upper INT
DECLARE @Lower INT
DECLARE @InsertDate DATETIME

3.Set on time :

SET @Lower = -730
SET @Upper = -1
SET @RowCount = 0

4.Populate the Table :

WHILE @RowCount < 3000000
BEGIN

5.Preparing Values

SET @RowString = CAST(@RowCount AS VARCHAR(10))

    SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

    SET @InsertDate = DATEADD(dd, @Random, GETDATE())

6. Write insert statment :

INSERT INTO TestTableSize
        (MyKeyField
        ,MyDate1
        ,MyDate2
        ,MyDate3
        ,MyDate4
        ,MyDate5)
    VALUES
        (REPLICATE('0', 10 - DATALENGTH(@RowString)) + @RowString
        , @InsertDate
        ,DATEADD(dd, 1, @InsertDate)
        ,DATEADD(dd, 2, @InsertDate)
        ,DATEADD(dd, 3, @InsertDate)
        ,DATEADD(dd, 4, @InsertDate))

    SET @RowCount = @RowCount + 1
END

7. Complete code :

DECLARE @RowCount INT
DECLARE @RowString VARCHAR(10)
DECLARE @Random INT
DECLARE @Upper INT
DECLARE @Lower INT
DECLARE @InsertDate DATETIME

SET @Lower = -730
SET @Upper = -1
SET @RowCount = 0

WHILE @RowCount < 3000000
BEGIN
    SET @RowString = CAST(@RowCount AS VARCHAR(10))
    SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
    SET @InsertDate = DATEADD(dd, @Random, GETDATE())

    INSERT INTO TestTableSize
        (MyKeyField
        ,MyDate1
        ,MyDate2
        ,MyDate3
        ,MyDate4
        ,MyDate5)
    VALUES
        (REPLICATE('0', 10 - DATALENGTH(@RowString)) + @RowString
        , @InsertDate
        ,DATEADD(dd, 1, @InsertDate)
        ,DATEADD(dd, 2, @InsertDate)
        ,DATEADD(dd, 3, @InsertDate)
        ,DATEADD(dd, 4, @InsertDate))

    SET @RowCount = @RowCount + 1
END

Answer 2

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.