Generate random names in sql

I have 5 random names each for male and female. I need to insert random names based on the gender. But how can we insert names in random from a set of 5 names in SQL. Is it possible?

  • append results of sql query
  • SQL Select 'n' records without a Table
  • How to find rows where a set of numbers is between two numbers?
  • Exec SP on Linked server and put that in temp table
  • What is a good approach in creating a non-NoSQL, relational multi-schema database?
  • Syncrhonizing 2 database with different schemas
  • 7 Solutions collect form web for “Generate random names in sql”

    select name from table order by newid()

    Create a table with the names, and an integer ID. Then use RAND() with % 5 to get down to a value between 0 and 4 inclusively. Add 1 if you want a male, and 6 if you want a female. Like so:

    Create table RandomNames
    (id int,
     name varchar(100),
     gender char(1)
    insert into RandomNames
    (id, name,gender)
    select 1,'Bill','M'
    select 2,'John','M'
    select 3,'Steve','M'
    select 4,'Mike','M'
    select 5,'Phil','M'
    select 6,'Sarah','F'
    select 7,'Ann','F'
    select 8,'Marie','F'
    select 9,'Liz','F'
    select 10,'Stephanie','F'
    declare @wantedGender char(1)
    select @wantedGender = 'M'
    select name 
    from RandomNames
    where id =  (CAST(RAND()*100 as int) % 5) + case when @wantedGender = 'M' then 1 else 6 end 

    Store the 5 random names for male in one table and the 5 random names for female in another table. Select a random integer between 1 and 5 and cross reference to male or female table using an inner join.

    Working on this problem currently.
    Rand() wont work, you’ll get the same Random number for all 300 rows. NewID() wont give you a value that is usable. You can however use the following formula for a random value between 1 and 5 on every line. NewID and Checksum given you a random number every time, but that includes negative numbers,then you divide by 4 and take the remainder (0-4) so you then take the absolute value of that number and add 1 to it. Then use that random number to select a value from the tables of names.

    ABS(Checksum(NewID()) % 4) + 1

    Using it in a command:

    Create table randomnames (ID int identity, name)
    insert into randomnames (name)
    Values ('Tom', 'Dick', 'Harry', 'Jughead', 'Archie'
              ,'Sally','Sue','Peggy', 'Betty', 'Veronica')
    update sometablename
    set Name = case gender
        when 'M' then (select name from randomnames where ID = ABS(Checksum(NewID()) % 4)) + 1
        when 'F' then (select name from randomnames where ID = ABS(Checksum(NewID()) % 4)) + 6

    Create a function

    CREATE FUNCTION [dbo].[getRandomName](@gen varchar(10))
    RETURNS @name varchar(50)
       SELECT TOP 1 @name = name FROM table WHERE gender=@gen ORDER BY newid()

    Then, just pass @gen to the function like this

    select dbo.getRandomName('male')

    Or, update many rows like this:

    UPDATE myNewTable 
       SET newName=dbo.getRandomName('male')
    WHERE gender='male'

    In SQL Server, the best way to get “random” is to use newid(). You can sort by this to get a sorted list.

    If you have five names for each gender, you can use a CTE to store them. The insert would then look like:

    with names as (
          select 'M' as gender, 'Alexander' as name union all
          select 'M', 'Burt' union all
          select 'M', 'Christopher' union all
          select 'M', 'Daniel' union all
          select 'M', 'Eric' union all
          select 'F', 'Alexandra' union all
          select 'F', 'Bertha' union all
          select 'F', 'Christine' union all
          select 'F', 'Daniela' union all
          select 'F', 'Erica'
    insert into table(name)
        select top 1 name
        from names
        where gender = @MyGender
        order by newid();
    select top 1 name from RandomNames 
    where gender = 'M' order by newid()

    Sample fiddle

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.