Saturday, October 20, 2012

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)

image

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.

2 comments:

  1. Pretty interesting post we really need this often thanks susantha!!!!

    ReplyDelete
  2. Hi Susanthab,
    Very nice article, But I have suggestion, if you can convert it to a tool, so that we just need to give schema and table name and it can generate sample data accordingly.

    ReplyDelete

Transitioning of SQLOS to SQLPAL

​ The SQLOS​ Prior to SQL Server 2005, SQL Server runs as a user mode process like any other Windows application and it depends on...