Tuesday, 22 September 2009

SQL Test Data Generator

Great script for generating test data in a SQL Server table:

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

set @db = N'aqs_Lead'
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 AQSProd2.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'
            when 'decimal' 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

Courtesy of Keith Bloom: http://stackoverflow.com/questions/1118960/create-test-data-in-sql-server