Mubashir Ali Memon Mubashir Ali Memon - 3 months ago 11
SQL Question

How to generate random data in SQL server

I want to create a stored procedure to insert random data in 'Video' table. I have already generated 30,000 record data for UserProfile table.

Note: The username is FK element in Video table.

CREATE TABLE UserProfile
(
Username VARCHAR(45) NOT NULL ,
UserPassword VARCHAR(45) NOT NULL ,
Email VARCHAR(45) NOT NULL ,
FName VARCHAR(45) NOT NULL ,
LName VARCHAR(45) NOT NULL ,
Birthdate DATE ,
Genger VARCHAR(10) NOT NULL ,
ZipCode INT ,
Image VARCHAR(50) ,

PRIMARY KEY(Username)
);
GO

CREATE TABLE Video
(
VideoId INT NOT NULL DEFAULT 1000 ,
Username VARCHAR(45) NOT NULL ,
VideoName VARCHAR(160) NOT NULL ,
UploadTime DATE ,
TotalViews INT ,
Thumbnail VARCHAR(100) ,

PRIMARY KEY(VideoId),
FOREIGN KEY(Username)
REFERENCES UserProfile(Username)
);
GO

Answer

It's not too difficult to generate random data, even in SQL

For example, to get a random username from your userprofile table.

BEGIN
-- get a random row from a table
DECLARE @username VARCHAR(50)
SELECT @username = [Username] FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY [Username]) [row], [Username]
    FROM [UserProfile]
) t 
WHERE t.row = 1 + (SELECT CAST(RAND() * COUNT(*) as INT) FROM [UserProfile])

print(@username)
END

To generate a random integer...

BEGIN
-- get a random integer between 3 and 7 (3 + 5 - 1)
DECLARE @totalviews INT
SELECT @totalviews = CAST(RAND() * 5 + 3 as INT)
print(@totalviews)
END

To generate a random varchar string

BEGIN
-- get a random varchar ascii char 32 to 128
DECLARE @videoname VARCHAR(160)
DECLARE @length INT
SELECT @videoname = ''
SET @length = CAST(RAND() * 160 as INT)
WHILE @length <> 0
    BEGIN
    SELECT @videoname = @videoname + CHAR(CAST(RAND() * 96 + 32 as INT))
    SET @length = @length - 1
    END
print(@videoname)
END

And finally, a random date

BEGIN
-- get a random datetime +/- 365 days
DECLARE @uploadtime DATETIME
SET @uploadtime = GETDATE() + (365 * 2 * RAND() - 365)
print(@uploadtime)
END