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

Care to share the solution to your problem?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.