0

today I have a bit of a dilemma. I was tasked with creating a local temporary table that would contain faculty members first name, last name, campus, and new id number. The ID number would be a randomly generated 5 digit number. (I am using Microsoft SQL Server Management Studio)

My problem is I am new to random number generation and local temp tables. I believe most of my code is correct expect for the "random id number" I need to make. I have googled my problem only thing is there seem to be many ways to create "random" numbers and I don't understand the method behind it. I've included my code and the database below.

My Code:

SELECT FirstName, LastName, Campus, LEFT(CAST(CAST(CEILING(RAND() *100000000) AS bigint) AS varchar), 5) AS IDnumber
INTO #LocalTemp1
FROM Faculty;

SELECT * FROM #LocalTemp1

Database:

CREATE TABLE Faculty
(Faculty_ID INT  PRIMARY KEY IDENTITY,
LastName VARCHAR (20) NOT NULL,
FirstName VARCHAR (20) NOT NULL,
Department VARCHAR (10) SPARSE NULL,
Campus VARCHAR (10) SPARSE NULL);

INSERT INTO Faculty VALUES ('Brown', 'Joe', 'Business', 'Kent'); 
INSERT INTO Faculty VALUES ('Smith', 'John', 'Economics', 'Kent'); 
INSERT INTO Faculty VALUES ('Jones', 'Sally', 'English', 'South'); 
INSERT INTO Faculty VALUES ('Black', 'Bill', 'Economics', 'Kent'); 
INSERT INTO Faculty VALUES ('Green', 'Gene', 'Business', 'South'); 


CREATE TABLE Course
(Course_ID INT PRIMARY KEY IDENTITY,
Ref_Number CHAR (5) CHECK (Ref_Number LIKE '[0-9][0-9][0-9][0-9][0-9]'),
Faculty_ID INT NOT NULL REFERENCES Faculty (Faculty_ID),
Term CHAR (1) CHECK (Term LIKE '[A-C]'),
Enrollment INT NULL DEFAULT 0 CHECK (Enrollment < 40))

INSERT INTO Course VALUES ('12345', 3, 'A', 24); 
INSERT INTO Course VALUES ('54321', 3, 'B', 18); 
INSERT INTO Course VALUES ('13524', 1, 'B', 7); 
INSERT INTO Course VALUES ('24653', 1, 'C', 29); 
INSERT INTO Course VALUES ('98765', 5, 'A', 35); 
INSERT INTO Course VALUES ('14862', 2, 'B', 14); 
INSERT INTO Course VALUES ('96032', 1, 'C', 8); 
INSERT INTO Course VALUES ('81256', 5, 'A', 5); 
INSERT INTO Course VALUES ('64321', 2, 'C', 23); 
INSERT INTO Course VALUES ('90908', 3, 'A', 38);

A source i was looking at, still need a better understanding: Generating a random & unique 8 character string using MySQL

EDIT: Running the query actually doesn't display any information, just the column names.

EDIT: Still would need help, I don't know if editing this post will bump it

EDIT: So I decided to try and just use "RAND()" on its own. I can now see the results being displayed, however, the number sets are not all random.

EDIT: Updated the formula for the RANDOM ID, it works in a way, just not making every row a unique random number.

Sutdent_Sequej
  • 49
  • 1
  • 7
  • `#LocalTemp1` is not how temporary tables are created in MySQL. Are you actually using SQL-Server? – Barmar Dec 07 '18 at 17:27
  • Do you need just to create a set of random numbers ? Or do you need to have a set of **unique** random numbers? The former is very easy to do, the latter is more complicated. – krokodilko Dec 07 '18 at 17:28
  • I need to create a unique random number i guess. Because the idea is when i run the query, it would display random numbers for each row. – Sutdent_Sequej Dec 07 '18 at 17:32

0 Answers0