How to generate random data in SQL server

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 tableDECLARE @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 INTSELECT @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 128DECLARE @videoname VARCHAR(160)DECLARE @length INTSELECT @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    ENDprint(@videoname)END

And finally, a random date

BEGIN-- get a random datetime +/- 365 daysDECLARE @uploadtime DATETIMESET @uploadtime = GETDATE() + (365 * 2 * RAND() - 365)print(@uploadtime)END

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?

    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