importing from one table to another
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
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
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.