Hi,

I am trying to build a query to import data from a flat table that I’ve imported into my sql database into a relational set of tables. The data is patient satisfactions surveys.

The flat table (Import_PatSatSurv) has a column for the clinicID, ten questions (Q1 – Q10), therapistInitials and DateSubmitted.

The relational tables are PatSatSurvey, QuestionScore, and SurveyQuestion and have the following columns:

PatSatSurvey: PatSatSurveyID (pk), ClinicID_TAOS, TherapistInitials, DateSubmitted, SubmitMethodID

QuestionScore: QuestionScoreID (pk), PatSatSurveyID (fk), SurveyQuestionID (fk), Score

SurveyQuestion: SurveyQuestionID (pk), QuestionText

*For SurveyQuestionID in table QuestionScore, the relationship between the flat table question columns Q1 – Q10 and the SurveyQuestionID is Q1 = 1, Q2 = 2, Q3 = 3, ..., Q10 = 10

My current query code is as follows. The first part, which inserts the unique individual survey into table PatSatSurvey, works (I checked this by verifying the number of records in table Import_PatSatSurv 2891 = number of records inserted into table PatSatSurvey).

The second part is not working correctly, and I am not quite sure why. I chose the first survey record, which had the following question Scores (scale 0 to 5): Q1 = 4, Q2 = 4, Q3 = 3, Q4 = 4, Q5 = 4, Q6 = 4, Q7 = 4, Q8 = 4, Q9 = 4, Q10 = 4

10 records should have been inserted into table QuestionScore for this survey, but instead 880 records were inserted. They also had values in the Score column that are not represented above (E.G., there were 65 records inserted for Q1 for this survey with values of 5. My thinking is that 2891 * 10 records should been inserted into table QuestionScore, but instead over 1,216,000 records were inserted.

The only column I am wondering about is QuestionScore.SurveyQuestionID, which is a fk lookup to table SurveyQuestion. However, values in this column 1 to 10 correspond directly to Import_PatSatSurv columns Q1 to Q10.


set nocount on

DECLARE @t TABLE(qnum int IDENTITY(1, 1) NOT NULL)

WHILE 1=1 begin
      INSERT INTO @t DEFAULT VALUES
      IF SCOPE_IDENTITY() > 9 BREAK
end

INSERT INTO PatSatSurvey(ClinicID_TAOS, TherapistInitials, DateSubmitted, SubmitMethodID) SELECT f.ClinicID_TAOS, f.TherapistID, f.DateEntered, 2 FROM Import_PatSatSurv f

INSERT INTO QuestionScore(PatSatSurveyID, SurveyQuestionID, Score) SELECT s.PatSatSurveyID, t.qnum
      , CASE t.qnum
            WHEN 1 THEN f.Q1
            WHEN 2 THEN f.Q2
            WHEN 3 THEN f.Q3
            WHEN 4 THEN f.Q4
            WHEN 5 THEN f.Q5
            WHEN 6 THEN f.Q6
            WHEN 7 THEN f.Q7
            WHEN 8 THEN f.Q8
            WHEN 9 THEN f.Q9
            WHEN 10 THEN f.Q10
      END

FROM Import_PatSatSurv f
INNER JOIN PatSatSurvey s ON s.ClinicID_TAOS = f.ClinicID_TAOS CROSS JOIN @t t

Thank you in advance – any help would be greatly appreciated.

John

Hello,

What is the ClinicID_TAOS value, and what are the resulting values that @t holds? I have a feeling that this error may be join-related...

Brian