Test data generator using T-SQL

Some times DBAs want to generate test data to test some applications designed and developed by themselves. I have faced many situations like this in past. Recently also I had to generate some test data to test one of my research projects. Below is the ERD of the application. It is just four tables.

1. Customer ( master data)
2. Item (master data)
3. SalesOrder (Sales order header data)
4. SalesOrderDetail (Sales order detail data)

I used AdventureWorks2012 database to load data into Customer and Item. I did not bother to write a script for this since it is pretty straight forward. The challenge was to generate data for SalesOrder and SalesOrderDetail. I used RAND() function throughout the script. You could simply use “GO n” where n is the no.of iterations you want to execute the script. This will simply load large data set quickly. You could customize the script to suite your requirement.

Note: You need data in Customer and Item tables prior to execute the script.

--Test data generator
--Generate Sales Order data
--Date: 10/20/2012

SET NOCOUNT ON
--declare local variables
DECLARE  @OrderID int
        ,@Customer_PK int 
        ,@Amount money
        ,@NoofOrderItems tinyint
        ,@loop tinyint
        ,@strSQL nvarchar(max)

--get random Order amount
SET @Amount=ROUND(CAST(RAND() * 1000 AS money),2)
--get random Customer
SET @Customer_PK= ISNULL((SELECT Customer_PK  from Customer WHERE Customer_PK=ROUND(CAST(RAND() * 100 AS int),0)*3),1)

--insert to Orders
INSERT dbo.SalesOrder (OrderDate,Amount,Customer_PK)
VALUES (GETDATE(),@Amount,@Customer_PK)

SET @OrderID=SCOPE_IDENTITY()

SET @NoofOrderItems=ROUND(CAST(RAND() * 10 AS int),0)

IF @NoofOrderItems=0
    SET @NoofOrderItems=1

SET @strSQL='INSERT dbo.SalesOrderDetail (Order_PK,Item_PK,Qty,UnitPrice) VALUES '
    
SET @loop=1
WHILE @loop<=@NoofOrderItems
BEGIN
        
    IF @loop=1
        SET @strSQL=@strSQL + '(' + CONVERT(varchar(10),@OrderID) + ',(SELECT ISNULL((SELECT ISNULL(Item_PK,1)  from Item WHERE Item_PK=ROUND(CAST(RAND() * 100 AS int),0)*3),1)),CAST(RAND() * 100 AS int),ROUND(CAST(RAND() * 100 AS money),2)*2)'
    ELSE
        SET @strSQL=@strSQL + ',(' + CONVERT(varchar(10),@OrderID) + ',(SELECT ISNULL((SELECT ISNULL(Item_PK,1)  from Item WHERE Item_PK=ROUND(CAST(RAND() * 100 AS int),0)*3),1)),CAST(RAND() * 100 AS int),ROUND(CAST(RAND() * 100 AS money),2)*2)'

    SET @loop=@loop+1

END

EXEC sp_executesql @strSQL

PRINT CONVERT(varchar,@@ROWCOUNT) + ' record(s) have been inserted into SalesOrderDetail table for Order_PK ' + CONVERT(varchar,@OrderID)

GO 100

Cheers for reading this post.