I have to make a database system that is purely on SQL Server. It's about a diagnostic lab. It should contain at least 40,000 distinct patient records. I have a table named "Patient" which contains an auto-generated ID, Name, DOB, Age and Phone number. Our teacher provided us with a dummy stored procedure which contained 2 temporary tables that has 200 names each and in the end he makes a Cartesian product which is supposed to give 40,000 distinct rows. I have used the same dummy stored procedure and modified it according to our table. But the rows inserted are only 1260 every time. Each time we run the query it does not give us more than 1260 records. I have added a part of temporary name tables and the stored procedure.
Declare @tFirstNames Table( FirstName Varchar(50) Not Null )
Declare @tLastNames Table ( LastName Varchar(50) Not Null )
Declare @tNames Table ( Id Int Identity Not Null, Name Varchar(50) Not Null)
Insert Into @tFirstNames (FirstName)
Select 'Julianne' Union All Select 'Sharyl' Union All Select 'Yoshie'
Union All Select 'Germaine' Union All Select 'Ja' Union All
Select 'Kandis' Select 'Hannelore' Union All Select 'Laquanda' Union All
Select 'Clayton' Union All Select 'Ollie' Union All
Select 'Rosa' Union All Select 'Deloras' Union All
Select 'April' Union All Select 'Garrett' Union All
Select 'Mariette' Union All Select 'Carline' Union All
Insert Into @tLastNames (LastName)
Select 'Brown' Union All Select 'Chrichton' Union All Select 'Bush'
Union All Select 'Clinton' Union All Select 'Blair'
Union All Select 'Wayne' Union All Select 'Hanks'
Union All Select 'Cruise' Union All Select 'Campbell'
Union All Select 'Turow' Union All Select 'Tracey'
Union All Select 'Arnold' Union All Select 'Derick'
Union All Select 'Nathanael' Union All Select 'Buddy'
Insert Into @tNames
Select FirstName + ' ' + LastName
From @tFirstNames, @tLastNames
Declare @iIndex Integer
Declare @iPatientTotalRecords Integer
Declare @vcName Varchar(50)
Declare @iAge Integer
--Set @iIndex = 1
Select @iPatientTotalRecords = Max(Id), @iIndex = Min(Id) From @tNames
While @iIndex <= @iPatientTotalRecords
Begin
Select @vcName = Name From @tNames Where Id = @iIndex
Set @iAge = Cast( Rand() * 70 As Integer ) + 10
Insert into Patient values
(@vcName, @iAge,
Case Cast( Rand() * 3 As Integer)
When 0 Then 'Male'
When 1 Then 'Female'
Else 'Female'
End,
Cast( Rand() * 8888889 As Integer ) + 1111111, DateAdd ( year, -@iAge, GetDate()))
Set @iIndex = @iIndex + 1
End
