Creating Random Names in SQL Server

Came across a problem when I needed a load of random names in an SQL Database (for an upcoming project on using maps in SSRS.. which I'll post up later). Looked around but couldn't find anything on the net so I decided to make some up. I Manged to get a list of forenames and surnames from the internet into a text file and decided to merge them up in SQL. For this to work you'll need to download the boys names, girls names and surnames then run the following SQL.

-- Lets Create some People.. a thousand will do
DECLARE @Counter BIGINT
SET @Counter = 1000

-- Create a table to put this in 
CREATE TABLE [Patients] ([Forename] VARCHAR(50), [SurName] VARCHAR(50), Gender BIT)
-- Sort out the Forenames we'll be using for the data, we make a #Name2 table because I have yet to figure our
-- inserting specific columns using BULK INSERT and without using a format file.
CREATE TABLE #Name (Name VARCHAR(50))
CREATE TABLE #ForeNames (FirstName VARCHAR(50), Gender BIT)
-- Move data in the #Name2 table
BULK INSERT #Name FROM "c:\Data\girlsforenames.txt" WITH (ROWTERMINATOR='\n')
-- Now move it to the forename table and add the gender
INSERT INTO #ForeNames SELECT [Name], 1 FROM #Name
-- Delete the names from temporary table
TRUNCATE TABLE #Name
-- Same for the boys
BULK INSERT #Name FROM "c:\Data\boysforenames.txt" WITH (ROWTERMINATOR='\n')
INSERT INTO #ForeNames SELECT [Name], 0 FROM #Name
-- Now do the surnames
TRUNCATE TABLE #Name
BULK INSERT #Name FROM "c:\Data\surnames.txt" WITH (ROWTERMINATOR='\n')

-- Now create these people, we'll be using the Names from above to create a random set of xxxx amount
SET @Counter = 0 
WHILE (@Counter > 0)
BEGIN
 INSERT INTO [Patients] ([Forename],[Surname],[Gender])
    SELECT TOP 1 #ForeNames.FirstName, (SELECT TOP 1 Name FROM #Name ORDER BY NEWID()), #ForeNames.Gender FROM #ForeNames ORDER BY NEWID()    
 SET @Counter=@Counter-1
END

DROP TABLE #Name
DROP TABLE #ForeNames

SELECT * FROM [Patients]

You can see for reading the files into the table I've used BULK INSERT which you can look up here since I find it a rather cool function.