Script to Randomly create names and SSN

Script to Randomly create names and SSN from RIS database


DECLARE @Firstnames TABLE(ID int identity(1,1), Firstname varchar(12))
DECLARE @LastNames TABLE(ID int identity(1,1), LastName varchar(20))
DECLARE @FirstNameCount int
DECLARE @LastNameCount int
DECLARE @PatientCount int
DECLARE @PatientID int

DECLARE @FirstNameRandom INT
DECLARE @LastNameRandom INT

DECLARE @Upper INT
DECLARE @Lower INT

INSERT INTO @Firstnames (Firstname)
SELECT FirstName FROM tbPatients GROUP BY FirstName

SELECT @FirstNameCount= Count(*) FROM @Firstnames

INSERT INTO @LastNames (LastName)
SELECT LastName FROM tbPatients GROUP BY LastName

SELECT @LastNameCount= Count(*) FROM @LastNames

SELECT @PatientCount=COUNT(*) FROM tbPatients

SELECT @FirstNameCount, @LastNameCount,@PatientCount

UPDATE tbPatients SET SSN=null


DECLARE cursor_Patients CURSOR FOR
SELECT PatientID FROM tbPatients
OPEN cursor_Patients
FETCH NEXT FROM cursor_Patients INTO @PatientID
WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @Lower=1
SET @Upper=@FirstNameCount
SELECT @FirstNameRandom = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

SET @Upper=@LastNameCount
SELECT @LastNameRandom = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

UPDATE tbPatients
SET FirstName = (SELECT Firstname FROM @Firstnames WHERE ID=@FirstNameRandom),
LastName = (SELECT LastName FROM @LastNames WHERE ID=@LastNameRandom)
WHERE PatientID=@PatientID

UPDATE tbPatients
SET SSN = (SELECT CONVERT(varchar(9),CONVERT(int,ROUND(((999999999 - 110000000 -1) * RAND() + 110000000), 0))))
WHERE PatientID=@PatientID

FETCH NEXT FROM cursor_Patients INTO @PatientID
END
CLOSE cursor_Patients
DEALLOCATE cursor_Patients


SELECT FirstName, LastName, SSN FROM tbPatients