Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 7 down vote accepted

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 SQL Server Sample Data (free, have not tested it), 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).

share|improve this answer
    
Thanks alot @aaron, i tried these tools they are amazing, the Red-gate SQL data generator is the best in my opinion, very straight forward and focused, the DTM is very generic and it gave errors especially with the way decimal is handeld in SQL, Arithmetic overflow error converting numeric to data type numeric, The SQL server sample data is not straight forward requires some reading and configuration, but yeah overall great tools, thanks again –  AmmarR Jun 27 '12 at 8:55
add comment

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

share|improve this answer
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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