Generating Random Int SQL Values between Upper and Lower Limits
This SQL mini-article is about generating random int values between upper and lower limits, in particular, to load data into demo database.
Introduction
This SQL tip is about generating random int
values between upper and lower limits.
You can also use the attached content to create a simple template database to make your own demos or tests.
Background
While I was updating my MiniBlog site by writing a new post about "pivoting data" (the subject for my new posts), I had the necessity to create new tables, in more detail, typical Sales and SalesDetails tables. So far, any problem, very easy.
After creating them, it was necessary to load these tables, Sales and SalesDetails, by generating random data but considering values stored in referenced (and typical basic) tables such as Customers
, Products
or Employees
. So, I thought that perhaps, it would be interesting for some to write a separate mini-article or tip about a simple manner to load these tables for demo purposes.
First of all, let's see all the tables related to this particular database to work with (if you want, you can go directly to "Using the code section" but I suggest you have a look to these tables):
Countries
Table (Referenced byCustomers
)CREATE TABLE Countries ( CountryId INT NOT NULL, ISO NVARCHAR(2) NOT NULL, ISO3 NVARCHAR(3) NOT NULL, ISONumeric INT NOT NULL, CountryName NVARCHAR(64) NOT NULL, Capital NVARCHAR(64) NOT NULL, ContinentCode NVARCHAR(2) NOT NULL, CurrencyCode NVARCHAR(3) NOT NULL, PRIMARY KEY(CountryId) )
Products
Table (Referenced bySalesDetails
)CREATE TABLE Products ( ProductId INT NOT NULL, Model varchar(50) NOT NULL, Cost decimal(18,2) NOT NULL, PRIMARY KEY(ProductId) );
Departments
Table (Referenced byEmployees
)CREATE TABLE Departments ( Id INT NOT NULL, Name NVARCHAR(50) NOT NULL, PRIMARY KEY(Id) );
Employees
Table (Referenced bySales
)CREATE TABLE Employees ( EmployeeId INT NOT NULL, FName NVARCHAR(50) NOT NULL, LName NVARCHAR(100) NOT NULL, PhoneNumber NVARCHAR(11), ManagerId INT, DepartmentId INT NOT NULL, Salary decimal(18,2) NOT NULL, HireDate DATETIME NOT NULL, PRIMARY KEY(EmployeeId), FOREIGN KEY (ManagerId) REFERENCES Employees(EmployeeId), FOREIGN KEY (DepartmentId) REFERENCES Departments(Id) );
Customers
Table (Referenced bySales
)CREATE TABLE Customers ( CustomerId INT NOT NULL, FName NVARCHAR(50) NOT NULL, LName NVARCHAR(100) NOT NULL, Email NVARCHAR(100) NOT NULL, PhoneNumber NVARCHAR(11), CountryId int null, FOREIGN KEY (CountryId) REFERENCES Countries(CountryId), PRIMARY KEY(CustomerId) );
- SALES TABLE
CREATE TABLE Sales ( Id INT NOT NULL, CustomerId INT NOT NULL, EmployeeId INT NOT NULL, OrderDate datetime not null, PRIMARY KEY(Id), FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId), FOREIGN KEY (EmployeeId) REFERENCES Employees(EmployeeId) );
- SALES DETAILS TABLE
CREATE TABLE SalesDetails ( SalesId INT NOT NULL, SalesDetailId int NOT NULL, ProductId INT NOT NULL, Quantity int not null, PRIMARY KEY(SalesId, SalesDetailId), FOREIGN KEY (ProductId) REFERENCES Products(ProductId) );
Besides, you can create these tables by downloading attached files to this post as long as you can load Countries
, Employees
, Customers
, Products
and Departments
tables in the same way.
Using the Code
At this moment, we're ready to load data in Sales
and SalesDetails
tables. So, to get random int
values, we just have to use SQL RAND()
function in a very simple way. The pseudocode to generate random values looks like:
SELECT @RandomValue = ROUND(((@UpperValue - @LowerValue -1) * RAND() + @LowerValue), 0)
Explanation:
Rand()
SQL function returns a number between0
and1
@UpperValue
contains the maximumint
value@LowerValue
contains the minimum int value@RandomValue
will be ourint
result after being rounded by theRound()
function. It uses value "0
" as length to delimitate decimal places. This way, the result will be anint
value.
Finally, the code (explained) to load Sales
and SalesDetails
table taking into account values in the rest of tables are:
set nocount on
begin tran
/* Example to load random data into "Sales" and "SalesDetails" tables */
declare @i int, @RowsToInsert int
set @RowsToInsert=100
set @i= (select MAX(Id) from Sales)+1
set @i=ISNULL(@i,1)
/* It finds upper and lower values for customers */
/* It requires not gaps between CustomerID values in Customers Table
If not, a check is necessary before inserting values.*/
declare @CustomerId int, @UpperCustomerId int, @LowerCustomerId int
select @UpperCustomerId=MAX(CustomerId),@LowerCustomerId=Min(CustomerId) from Customers
/* Similar to previous query */
declare @EmployeeId int, @UpperEmployeeId int, @LowerEmployeeId int
select @UpperEmployeeId=MAX(EmployeeId),@LowerEmployeeId=Min(EmployeeId) from Employees
where DepartmentId=2 /* It filters by "Sales" Department */
/* Similar to previous query */
declare @ProductId int, @UpperProductId int, @LowerProductId int
select @UpperProductId=MAX(ProductId),@LowerProductId=Min(ProductId) from Products
/* Calculate upper limit for loop */
declare @UpperRowLimit int
set @UpperRowLimit = @i + @RowsToInsert
/* Loop */
while @i < @UpperRowLimit
begin
/* Obtain random values for @CustomerId between limits */
SELECT @CustomerId =ROUND(((@UpperCustomerId - @LowerCustomerId -1) * RAND() + @LowerCustomerId), 0)
/* Obtain random values for @EmployeeId between limits */
SELECT @EmployeeId =ROUND(((@UpperEmployeeId- @LowerEmployeeId -1) * RAND() + @LowerEmployeeId), 0)
/* Insert "header" data into "Sales" table */
insert into Sales(Id, CustomerId, EmployeeId,
OrderDate)
select @i, @CustomerId, @EmployeeId,
GETDATE() - ROUND(365 * RAND(),2) /* Random value for OrderDate as well */
/* Obtain random values for @ProductId between limits */
SELECT @ProductId = ROUND(((@UpperProductId - @LowerProductId -1) * RAND() + @LowerProductId), 0)
/* Insert "details" data into "SalesDetails" table */
insert into SalesDetails(SalesId, SalesDetailId,ProductId,
Quantity)
select @i, 1, @ProductId,
round(RAND() + 1,0) as Quantity /* Random value between 1 and 2 */
if (RAND() > 0.5)
BEGIN
/* With a 50% of probability inserts a second new row for details */
SELECT @ProductId = ROUND(((@UpperProductId - @ProductId -1) * RAND() + @ProductId), 0)
if (@ProductId > 0)
begin
insert into SalesDetails(SalesId, SalesDetailId,ProductId,
Quantity)
select @i, 2, @ProductId,
round(RAND() + 1,0) as Quantity /* Random value between 1 and 2 */
end
END
set @i=@i+1
end
commit tran
The process is simple:
- We look for the upper and lower value in referenced tables, saving them in different variables.
- Iterate a preset number of times generating random values according to previous limiting values for each referenced table, calculating new values for primary keys and finally inserting new rows.
Points of Interest
As you can deduce easily, this solution requires no gaps for values in Customers
, Products
or Employees
tables. Otherwise, it would be mandatory testing the generating random value before inserting it to avoid foreign key errors.
For demo purposes, I have not checked values because there are no gaps in my tables.
Regards!