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.
- Customer ( master data)
- Item (master data)
- SalesOrder (Sales order header data)
- 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
SET NOCOUNT ON
--declare local variables
DECLARE @OrderID int
--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)
SET @NoofOrderItems=ROUND(CAST(RAND() * 10 AS int),0)
SET @strSQL='INSERT dbo.SalesOrderDetail (Order_PK,Item_PK,Qty,UnitPrice) VALUES '
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 @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)'
EXEC sp_executesql @strSQLPRINT CONVERT(varchar,@@ROWCOUNT) + ' record(s) have been inserted into SalesOrderDetail table for Order_PK ' + CONVERT(varchar,@OrderID)
GO 100Cheers for reading this post.