Click to download Source Code
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 by Customers)
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 by SalesDetails)
CREATE TABLE Products (
ProductId INT NOT NULL,
Model varchar(50) NOT NULL,
Cost decimal(18,2) NOT NULL,
PRIMARY KEY(ProductId)
);
- Departments Table (Referenced by Employees)
CREATE TABLE Departments (
Id INT NOT NULL,
Name NVARCHAR(50) NOT NULL,
PRIMARY KEY(Id)
);
- Employees Table (Referenced by Sales)
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 by Sales)
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)
);
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)
);
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 return a number between 0 and 1
- @UpperValue contains the maximum int value
- @LowerValue contains the minimum int value
- @RandomValue will be our int result after being rounded by the Round() function. It uses value "0" as length to delimitate decimal places. This way the result will be an int 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 Employeestables. 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.