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.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.|||EXCELLENT - I knew it had to be simple. But unless you know it just isn't going to happen.

So thanks for this.

Ian.

:D

How do I do difference in this case?

I have a customers table that looks like this: custid, name, city
I have a temp table that looks like this: name, city
I want to find all the records in the temp table that do not exists in the
customers table.
There are no nulls or duplicate name/city fields in either table.
If I had a custid field in the temp table I could do a simple outer join and
look for the NULL customer table values to find the temp records that did
not exist in the customers table.
But I can't get this to work matching on the name/city fields.
Can someone help me out with this?SELECT * FROM TempTable AS a
WHERE NOT EXISTS (SELECT * FROM RealTable AS b WHERE b.Name = a.name and
b.city = a.city)
Andrew J. Kelly SQL MVP
"Dave" <dave@.nospam.ru> wrote in message
news:%2308WBS2JFHA.3992@.TK2MSFTNGP15.phx.gbl...
>I have a customers table that looks like this: custid, name, city
> I have a temp table that looks like this: name, city
> I want to find all the records in the temp table that do not exists in the
> customers table.
> There are no nulls or duplicate name/city fields in either table.
> If I had a custid field in the temp table I could do a simple outer join
> and
> look for the NULL customer table values to find the temp records that did
> not exist in the customers table.
> But I can't get this to work matching on the name/city fields.
> Can someone help me out with this?
>

How do I do difference in this case?

I have a customers table that looks like this: custid, name, city
I have a temp table that looks like this: name, city
I want to find all the records in the temp table that do not exists in the
customers table.
There are no nulls or duplicate name/city fields in either table.
If I had a custid field in the temp table I could do a simple outer join and
look for the NULL customer table values to find the temp records that did
not exist in the customers table.
But I can't get this to work matching on the name/city fields.
Can someone help me out with this?SELECT * FROM TempTable AS a
WHERE NOT EXISTS (SELECT * FROM RealTable AS b WHERE b.Name = a.name and
b.city = a.city)
Andrew J. Kelly SQL MVP
"Dave" <dave@.nospam.ru> wrote in message
news:%2308WBS2JFHA.3992@.TK2MSFTNGP15.phx.gbl...
>I have a customers table that looks like this: custid, name, city
> I have a temp table that looks like this: name, city
> I want to find all the records in the temp table that do not exists in the
> customers table.
> There are no nulls or duplicate name/city fields in either table.
> If I had a custid field in the temp table I could do a simple outer join
> and
> look for the NULL customer table values to find the temp records that did
> not exist in the customers table.
> But I can't get this to work matching on the name/city fields.
> Can someone help me out with this?
>

How do I do difference in this case?

I have a customers table that looks like this: custid, name, city
I have a temp table that looks like this: name, city
I want to find all the records in the temp table that do not exists in the
customers table.
There are no nulls or duplicate name/city fields in either table.
If I had a custid field in the temp table I could do a simple outer join and
look for the NULL customer table values to find the temp records that did
not exist in the customers table.
But I can't get this to work matching on the name/city fields.
Can someone help me out with this?
SELECT * FROM TempTable AS a
WHERE NOT EXISTS (SELECT * FROM RealTable AS b WHERE b.Name = a.name and
b.city = a.city)
Andrew J. Kelly SQL MVP
"Dave" <dave@.nospam.ru> wrote in message
news:%2308WBS2JFHA.3992@.TK2MSFTNGP15.phx.gbl...
>I have a customers table that looks like this: custid, name, city
> I have a temp table that looks like this: name, city
> I want to find all the records in the temp table that do not exists in the
> customers table.
> There are no nulls or duplicate name/city fields in either table.
> If I had a custid field in the temp table I could do a simple outer join
> and
> look for the NULL customer table values to find the temp records that did
> not exist in the customers table.
> But I can't get this to work matching on the name/city fields.
> Can someone help me out with this?
>