Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 3rd, 2004, 10:41 AM
Authorized User
 
Join Date: Jun 2003
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default importing from one table to another

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


 
Old June 3rd, 2004, 11:34 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing from Excel to Access table DeborahP Excel VBA 3 April 3rd, 2007 03:21 AM
Importing CSV inspiron SQL Server DTS 1 October 24th, 2006 03:17 PM
Importing a WebTable into an SQL Table cej2583 SQL Server DTS 0 October 24th, 2005 01:28 PM
Importing Hyperlinks into Access table Sammy8932 Access VBA 0 May 11th, 2005 09:23 AM
Importing text data to an access table sbyers_1982 VBScript 4 November 29th, 2004 01:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.