SQL Server Creating a Temporary Table that has Random Number for Every Row

Question

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'),
       ('Smith', 'John', 'Economics', 'Kent'),
       ('Jones', 'Sally', 'English', 'South'),
       ('Black', 'Bill', 'Economics', 'Kent'),
       ('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), ('54321', 3, 'B', 18),
       ('13524', 1, 'B', 7), ('24653', 1, 'C', 29),
       ('98765', 5, 'A', 35), ('14862', 2, 'B', 14),
       ('96032', 1, 'C', 8), ('81256', 5, 'A', 5),
       ('64321', 2, 'C', 23), ('90908', 3, 'A', 38);

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

Reposting this because a lot of things were incorrect my last one such as tags and explanations.

Just for fun, [here is an example](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3055e655b8a3e0c8fdd5d824c261dbc6) — Ilyes, Dec 08 '18 at 17:56
@Sami i see thats very interesting and will refer back to this if needed :) — Sutdent_Sequej, Dec 08 '18 at 18:33

score 3 · Answer 1 · answered Dec 08 '18 at 16:53

3

Generating a unique number is really a pain. I would suggest that you generate random numbers, but in a sequence:

SELECT FirstName, LastName, Campus,
       ROW_NUMBER() OVER (ORDER BY NEWID()) as IDnumber
INTO #LocalTemp1
FROM Faculty;

You can, say, add 10,000 to the number if you want a 5-digit number that doesn't start with 0.

answered Dec 08 '18 at 16:53

Gordon Linoff

1,242,037
58
646
786

Oh I see! But since it needs to be 5 digits, how would i do that with your formula? btw I appreciate the response! – Sutdent_Sequej Dec 08 '18 at 16:57
By adding the 10,000 you're weeding out the 4-and-less-digit values...and assuming there are no more than 90,000 faculty members, you're there! – Clay Dec 08 '18 at 17:17
@Sutdent_Sequej You mean like `00001` -> `99999`? – Ilyes Dec 08 '18 at 17:17
@Sami, I think the point is 0001-9999 are not eligible values. At least that's what the OP seems to suggest. – Clay Dec 08 '18 at 17:19
Yes that was what i meant @Sami however, I see what you guys mean. Create a range for the numbers right? – Sutdent_Sequej Dec 08 '18 at 17:24

score 0 · Answer 2 · answered Dec 08 '18 at 18:31

To anyone wondering, I did end up solving my own question! Thought I would share it here.

CREATE TABLE #Rand4
(
    LocalID int IDENTITY,
    IDnumber char(5) DEFAULT
                LEFT(CAST(CAST(CEILING(RAND() * 100000000) AS bigint) AS varchar), 5)
);

INSERT #Rand4 VALUES (DEFAULT);
INSERT #Rand4 VALUES (DEFAULT);
INSERT #Rand4 VALUES (DEFAULT);
INSERT #Rand4 VALUES (DEFAULT);
INSERT #Rand4 VALUES (DEFAULT);

SELECT FirstName, LastName, Campus, IDnumber
FROM Faculty JOIN #Rand4 
    ON Faculty.Faculty_ID = #Rand4.LocalID;

The problem with my initial query was that I wasn't taking into account I had to create the local table around the random number function. Hope this helps!

Thank you for the help @clay and @Sami!

SQL Server Creating a Temporary Table that has Random Number for Every Row

2 Answers2