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

@AaronBertrand If this was a free tool I would agree with you, but it's nearly $400! — Phillip Copley
– Phillip Copley, Commented May 25, 2016 at 17:54
@PhillipCopley That's only a problem, really, if you and/or your employer don't place any value on your time and you are fully capable of solving the same problem in an equal or better way. There are reasons people buy software - usually it's because that's more cost effective than writing their own and/or the free versions don't fulfill all requirements. Please read: The cost of reinventing the wheel. — Aaron Bertrand
– Aaron Bertrand, Commented May 25, 2016 at 18:01

Louis Ricci · Accepted Answer · 2013-07-31 18:29:02Z

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

Community · Accepted Answer · 2017-05-23 11:54:28Z

10

As Aaron already suggested in his comment – I wouldn’t really reinvent the wheel.

Just go and find some of the already available tools for this.

Here is an article on how to do this in VS and thread here on StackOverflow.

Data generators for SQL server?

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Aug 1, 2013 at 9:34

Thomas Bovee

9338 silver badges4 bronze badges

Comments

Michał Lepczyński · Accepted Answer · 2019-11-19 12:34:14Z

3

    declare @i int
    set @i = 0 
    while (@i < 7)


    Begin

    BEGIN
    -- get a random row from a table
    DECLARE @username VARCHAR(50)
    DECLARE @length INT
    SELECT @username = ''
    SET @length = CAST(RAND() * 50 as INT)
    WHILE @length <> 0
        BEGIN
        SELECT @username = @username + CHAR(CAST(RAND() * 96 + 32 as INT))
        SET @length = @length - 1
        END
    END


    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

    BEGIN
    -- get a random varchar ascii char 32 to 128
    DECLARE @videoname VARCHAR(160)

    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
    END

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

    insert into table_1 values(@videoname, @username, @totalviews, @length, @uploadtime)
    SET @i = @i + 1
    end

edited Nov 19, 2019 at 12:34

Michał Lepczyński

4335 silver badges12 bronze badges

answered Mar 23, 2017 at 13:00

Alyami

311 bronze badge

1 Comment

jjj Over a year ago

please add some description explaining answer.

user9944315 · Accepted Answer · 2020-10-20 23:51:06Z

2

http://www.generatedata.com/#generator This is great. Limits you to 300 rows or so. But, works well. The paid version generates more data.

answered Oct 20, 2020 at 23:51

user9944315

3412 silver badges4 bronze badges

Collectives™ on Stack Overflow

How to generate random data in SQL server

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related