Results 1 to 3 of 3
  1. #1
    Join Date
    May 2006
    Location
    Melbourne
    Posts
    6

    Unanswered: How do I insert into existing Temp table?

    Hi,

    How do I insert data into an existing temporary table? Note: Im primarily a .NET programmer who has to do T-SQL to grab data from time to time.

    What I am trying to do is this:
    1) Put the scores for all the people who have completed a questionnaire into a temporary table called #GroupConfidence.
    2) Add on a row at the end that gives an average for each score (ie the last row is an average of the column above).

    I need my SP to give me a DataSet that I can throw straight to my .NET reporting engine (I dont want to do any number crunching inside .NET) - that's why I want to add on the 'average' row at the end.


    If I do this (below) the temporary table (#GroupConfidence) gets created and the values inserted.

    -- Insert the results into the #GroupConfidence table
    SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
    RP.SubmitID,
    RP.GL_Score,
    RP.GP_Score,
    RP.GPH_Score,
    RP.DL_Score,
    RP.MP_Score,
    RP.Role_MI_Score,
    RP.Role_ASXRE_Score,
    RP.Role_APRA_Score,
    RP.Overall_Score AS 'AllCategories'
    INTO #GroupConfidence
    FROM RodResultPercentages RP
    JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID

    My problem is that #GroupConfidence already exists so in fact I have this code below:

    CREATE TABLE #GroupConfidence
    ( [FullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [SubmitID] [int] NOT NULL,
    [GL_Score] [decimal](19, 10) NOT NULL,
    [GP_Score] [decimal](19, 10) NOT NULL,
    [GPH_Score] [decimal](19, 10) NOT NULL,
    [DL_Score] [decimal](19, 10) NOT NULL,
    [MP_Score] [decimal](19, 10) NOT NULL,
    [Role_MI_Score] [decimal](19, 10) NOT NULL,
    [Role_ASXRE_Score] [decimal](19, 10) NOT NULL,
    [Role_APRA_Score] [decimal](19, 10) NOT NULL,
    [AllCategories] [decimal](19, 10) NOT NULL
    )

    -- Insert the results into the #GroupConfidence table
    SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
    RP.SubmitID,
    RP.GL_Score,
    RP.GP_Score,
    RP.GPH_Score,
    RP.DL_Score,
    RP.MP_Score,
    RP.Role_MI_Score,
    RP.Role_ASXRE_Score,
    RP.Role_APRA_Score,
    RP.Overall_Score AS 'AllCategories'
    INTO #GroupConfidence
    FROM RodResultPercentages RP
    JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID

    So I get this error: Server: Msg 2714, Level 16, State 1, Line 109
    There is already an object named '#GroupConfidence' in the database.



    Thanks in advance,

    Ian.

  2. #2
    Join Date
    Aug 2004
    Posts
    76
    SELECT ..... INTO <NEWTABLE> FROM <ANOTHER TABLE> will create the table and then insert the rows of the SELECT statement. You are getting the error because first you had created the table using "CREATE TABLE" statement then again are using SELECT ... INTO statment to create the table and insert the rows.

    If you want to perform CREATE and INSERT operations in two statements then you can execute below statements:

    CREATE TABLE #GroupConfidence ....

    INSERT INTO #GroupConfidence SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
    RP.SubmitID,
    RP.GL_Score,
    RP.GP_Score,
    RP.GPH_Score,
    RP.DL_Score,
    RP.MP_Score,
    RP.Role_MI_Score,
    RP.Role_ASXRE_Score,
    RP.Role_APRA_Score,
    RP.Overall_Score AS 'AllCategories' FROM RodResultPercentages RP
    JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID

    Hope the above helps you.

  3. #3
    Join Date
    May 2006
    Location
    Melbourne
    Posts
    6
    EXCELLENT - I knew it had to be simple. But unless you know it just isn't going to happen.

    So thanks for this.

    Ian.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •