Database Challenges

/***********************************************************************
 ---------------------------------------------- 
 Load Testing Data for TSQL Challenge 19
 Copyright © beyondrelational.com
 ---------------------------------------------- 

 Notes:
 1. 

 Revision History:
 Rev 00 - 20 April 2010 - Tomas Boixet	- Initial Release
***********************************************************************/

-- Parameters
DECLARE 
	@levels int, 
	@top_level int, 
	@sons int, 
	@orders_emp int;
	
SET @levels=6;		-- Hierarchy levels
SET @top_level=3;	-- Top-level employees
SET @sons=3;		-- Number of sons for each employe
SET @orders_emp=50;	-- Number of orders per employe

-- compute records
DECLARE 
	@numemp int, 
	@numorders int, 
	@n int, 
	@n1 int;
	
SET @n=1;
SET @numemp=@top_level;
SET @n1=@numemp;

WHILE @n<@levels BEGIN 
	SET @n1=@n1*@sons 
	SET @numemp=@numemp+@n1 
	SET @n=@n+1 
END;

PRINT 'Employees: '+CONVERT(varchar,@numemp);
PRINT 'Orders: '+CONVERT(varchar,@numemp*@orders_emp);

-- Create tables
IF OBJECT_ID('TC19_Employees','U') IS NOT NULL 
	DROP TABLE TC19_Employees;
	
CREATE TABLE TC19_Employees(
	EmployeeID INT NOT NULL,
	FirstName VARCHAR(30) NULL,
	LastName VARCHAR(30) NULL,
	ReportsTo INT NULL,
	CONSTRAINT PK_TC19_Employees PRIMARY KEY (EmployeeID ASC) );
CREATE INDEX IX_TC19_Employees ON TC19_Employees (ReportsTo ASC);

IF OBJECT_ID('TC19_Orders','U') IS NOT NULL 
	DROP TABLE TC19_Orders;
	
CREATE TABLE TC19_Orders(
	OrderID INT IDENTITY(1,1) NOT NULL,
	EmployeeID INT NULL,
	CONSTRAINT [PK_TC19_Orders] PRIMARY KEY (OrderID ASC) );
CREATE INDEX IX_TC19_Orders ON TC19_Orders (EmployeeID ASC);

-- Fill tables
WITH e (EmployeeID, FirstName, LastName, ReportsTo, lvlid) AS
(
	SELECT 
		CAST(N AS INT), 
		'FirstName'+CONVERT(varchar, N), 
		'LastName'+CONVERT(varchar, N), 
		CAST(NULL AS int), 
		1
	FROM dbo.tsqlc_Tally 
	WHERE N BETWEEN 1 AND @top_level
	UNION ALL
	SELECT 
		CONVERT(INT, (EmployeeID*@sons)-(N-1)+@top_level),
		'FirstName'+CONVERT(varchar, (EmployeeID*@sons)-(N-1)+@top_level), 
		'LastName'+CONVERT(varchar, (EmployeeID*@sons)-(N-1)+@top_level), 
		EmployeeID, 
		1+lvlid
	FROM e 
	CROSS JOIN dbo.tsqlc_Tally
	WHERE N BETWEEN 1 AND @sons AND lvlid<@levels
) 
INSERT INTO TC19_Employees (
	EmployeeID, 
	FirstName, 
	LastName, 
	ReportsTo)
SELECT 
	EmployeeID, 
	FirstName, 
	LastName, 
	ReportsTo 
FROM e;

INSERT INTO TC19_Orders (EmployeeID)
SELECT 
	EmployeeID
FROM TC19_Employees
CROSS JOIN dbo.tsqlc_Tally
WHERE N BETWEEN 1 AND @orders_emp;