A Script to insert dummy Data in all tables of Database

Question

I wanted a Script that can loop through all tables and their columns in the database and insert dummy data based on column type and size, so that i can start using the database for testing, and ability to run queries and Test Apps.

Answer 1

There are lots of cases that @AmmarR's solution doesn't handle - ROWVERSION/TIMESTAMP, computed columns, columns with check constraints, foreign keys, UNIQUEIDENTIFIER columns that default to NEWSEQUENTIALID(), date/time columns that default to GETDATE(), sparse columns, etc. etc.

Why reinvent the wheel? There are tools out there that can generate data for you and they've probably already thought about all of these restrictions. For example DTM Data Generator (not free, have not tested it) and Red-Gate SQL Data Generator (not free, have tested it and recommend it - also reviewed here).

Answer 2

Am sure there are different and better ways to get this task done e.g using cursors or sql functions, etc i thought of sharing my way of getting the task done.

beginning create a stored procedure that will generate and execute the insert statements that inserts the dummy data The stored procedure takes in 1 parameter, the parameter is the no. of rows to be added to all tables

the logic of the script is simple, a select of sys.columns joined with sys.types & sys.tables

The sys.columns has all column names the sys.types has datatype of the column the sys.tables has table name, there are other ways to get table name like object_name(c.object_id) but i thought that joining sys.columns with sys.tables is more convenient

below is the code

Create PROCEDURE [dbo].[GenerateandExecuteInsertStatmentsforTables] 
    @NoOfRows int
AS
BEGIN

Declare @icount int
declare @qry varchar(max)
set @qry = ' '
set @icount = 0
while @icount < @NoOfRows
begin
    select @qry = @qry
    +
    case when c.column_id = 1 then
        'insert into ' + SCHEMA_NAME(t.schema_id) + '.[' + t.name + '] values('
    else
        ''
    end
    +
    -- incase the column is identity, i dont include it in the insert
    case when c.is_identity = 0 then
        case when ty.name in ('bit','bigint','int','smallint','tinyint','float','decimal','numeric','money','smallmoney','real') then
            substring(CAST(  round(RAND() * 1000,0) AS varchar),1,c.max_length)
        when ty.name in ('binary','varbinary') then
            substring('0x546869732069732044756D6D792044617461',1,c.max_length)
        when ty.name In ('varchar','char','text') then
            '''' + substring('Dummy This is Dummy Data',1,c.max_length)  + '''' 
        when ty.name In ('nchar','nvarchar','ntext') then
            '''' + substring('Dummy This is Dummy Data',1,c.max_length / 2)  + ''''             
        when ty.name in('date','time','datetime','datetime2','smalldatetime','datetimeoffset') then
            '''' + convert(varchar(50),dateadd(D,Round(RAND() * 1000,1),getdate()),121) + '''' 
        when ty.name in ('uniqueidentifier') then
            cast(NEWID() as varchar(33))
        else
            ''
        end
        + 
        case when c.column_id = (Select MAX(insc.column_id) from sys.columns insc where insc.OBJECT_ID = c.OBJECT_ID) then
            ');'
        else
            ','
        end
    else
        ''  
    end

    FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
    INNER JOIN sys.types AS ty ON c.user_type_id=ty.user_type_id
    ORDER BY t.name,c.column_id; 
    set @icount = @icount + 1

    --execute the insert statments
    --Select (@qry)
    exec (@qry)
    Set @qry = ' ' 
end

after that i execute the store procedure

USE [DBNAME]
EXEC [dbo].[GenerateandExecuteInsertStatmentsforTables] 5

This will add 5 rows to all tables in the database