How to generate random data in SQL server

Question

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 1

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