4

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.

9

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).

0
3

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

2
  • 1
    As I mentioned in my answer, this leaves out a lot of possible complications. In addition to those I've mentioned (some of which being columns you need to leave out, and others where you need to ensure the "random" data is valid), your INSERT doesn't list the column names - what guarantee do you have that the generated insert statement is going to list the columns in the right order? It may be what you normally observe but without a guarantee I don't trust it. – Aaron Bertrand Jun 26 '12 at 12:40
  • 2
    Also I would prefer set based inserts rather than RBAR and a "nice to have" would be an option to configure column selectivity. – Martin Smith Jun 26 '12 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.