Using Temp Table to Insert Query Results 1

[Cheryl3D]

Hi:
How can I insert the results of a query (generated via a stored procedure) into a temporary table and repeatedly reuse the results inserted to perform some aggregate calculations?

I have a stored procedure that takes several views that I created from one table. Using CASE statements within the stored procedure (sp_CustSurveyData), I take each view and generate what is the equivalent to a cross tab (like in MS Access) for each view. Then I take each of the crosstab results and merge them into one result set using the UNION statement.

The stored procedure is as follows:

CREATE PROCEDURE sp_CustSurveyData AS
SELECT record, datesubmitted, 'Appearance' As Category,
CASE Appearance
WHEN 'OUTSTANDING' THEN 1 ELSE 0 END AS OUTSTANDING,
CASE Appearance
WHEN 'EXCELLENT' THEN 1 ELSE 0 END AS EXCELLENT,
CASE Appearance
WHEN 'GOOD' THEN 1 ELSE 0 END AS GOOD,
CASE Appearance
WHEN 'AVERAGE' THEN 1 ELSE 0 END AS AVERAGE,
CASE Appearance
WHEN 'POOR' THEN 1 ELSE 0 END AS POOR
FROM vw_custsurv_appearance
Union
SELECT record, datesubmitted, 'Prompt' As Category,
CASE Prompt
WHEN 'OUTSTANDING' THEN 1 ELSE 0 END AS OUTSTANDING,
CASE Prompt
WHEN 'EXCELLENT' THEN 1 ELSE 0 END AS EXCELLENT,
CASE Prompt
WHEN 'GOOD' THEN 1 ELSE 0 END AS GOOD,
CASE Prompt
WHEN 'AVERAGE' THEN 1 ELSE 0 END AS AVERAGE,
CASE Prompt
WHEN 'POOR' THEN 1 ELSE 0 END AS POOR
FROM vw_custsurv_prompt
Union
SELECT record, datesubmitted, 'Courteous' As Category,
CASE Courteous
WHEN 'OUTSTANDING' THEN 1 ELSE 0 END AS OUTSTANDING,
CASE Courteous
WHEN 'EXCELLENT' THEN 1 ELSE 0 END AS EXCELLENT,
CASE Courteous
WHEN 'GOOD' THEN 1 ELSE 0 END AS GOOD,
CASE Courteous
WHEN 'AVERAGE' THEN 1 ELSE 0 END AS AVERAGE,
CASE Courteous
WHEN 'POOR' THEN 1 ELSE 0 END AS POOR
FROM vw_custsurv_courteous
Union
SELECT record, datesubmitted,' professionalism' As Category,
CASE Professionalism
WHEN 'OUTSTANDING' THEN 1 ELSE 0 END AS OUTSTANDING,
CASE Professionalism
WHEN 'EXCELLENT' THEN 1 ELSE 0 END AS EXCELLENT,
CASE Professionalism
WHEN 'GOOD' THEN 1 ELSE 0 END AS GOOD,
CASE Professionalism
WHEN 'AVERAGE' THEN 1 ELSE 0 END AS AVERAGE,
CASE Professionalism
WHEN 'POOR' THEN 1 ELSE 0 END AS POOR
FROM vw_custsurv_professionalism
Union
SELECT record, datesubmitted, 'MetExpectations' As Category,
CASE MetExpectations
WHEN 'OUTSTANDING' THEN 1 ELSE 0 END AS OUTSTANDING,
CASE MetExpectations
WHEN 'EXCELLENT' THEN 1 ELSE 0 END AS EXCELLENT,
CASE MetExpectations
WHEN 'GOOD' THEN 1 ELSE 0 END AS GOOD,
CASE MetExpectations
WHEN 'AVERAGE' THEN 1 ELSE 0 END AS AVERAGE,
CASE MetExpectations
WHEN 'POOR' THEN 1 ELSE 0 END AS POOR
FROM vw_custsurv_MetExpectation
Union
SELECT record, datesubmitted, 'overallrating' As Category,
CASE overallrating
WHEN 'OUTSTANDING' THEN 1 ELSE 0 END AS OUTSTANDING,
CASE overallrating
WHEN 'EXCELLENT' THEN 1 ELSE 0 END AS EXCELLENT,
CASE overallrating
WHEN 'GOOD' THEN 1 ELSE 0 END AS GOOD,
CASE overallrating
WHEN 'AVERAGE' THEN 1 ELSE 0 END AS AVERAGE,
CASE overallrating
WHEN 'POOR' THEN 1 ELSE 0 END AS POOR
FROM vw_custsurv_overall
Union
SELECT record, datesubmitted, 'quality' As Category,
CASE quality
WHEN 'OUTSTANDING' THEN 1 ELSE 0 END AS OUTSTANDING,
CASE quality
WHEN 'EXCELLENT' THEN 1 ELSE 0 END AS EXCELLENT,
CASE quality
WHEN 'GOOD' THEN 1 ELSE 0 END AS GOOD,
CASE quality
WHEN 'AVERAGE' THEN 1 ELSE 0 END AS AVERAGE,
CASE quality
WHEN 'POOR' THEN 1 ELSE 0 END AS POOR
FROM vw_custsurv_quality


After the stored procedure is executed, the result set looks like the following:

record datesubmitted Category OUTSTANDING EXCELLENT GOOD AVERAGE POOR
-------- -------------- -------------- ---------------- ----------- ----------- ----------- ----------- -----------
3 Sep 25 2002 2:43PM professionalism 0 0 0 1 0
3 Sep 25 2002 2:43PM Appearance 0 0 0 0 1
3 Sep 25 2002 2:43PM Courteous 0 0 0 1 0
3 Sep 25 2002 2:43PM MetExpectations 0 0 0 1 0
3 Sep 25 2002 2:43PM overallrating 0 0 0 1 0
3 Sep 25 2002 2:43PM Prompt 0 0 0 1 0
3 Sep 25 2002 2:43PM quality 0 1 0 0 0
4 Sep 25 2002 3:57PM professionalism 0 1 0 0 0
4 Sep 25 2002 3:57PM Appearance 0 0 0 1 0
4 Sep 25 2002 3:57PM Courteous 0 1 0 0 0
4 Sep 25 2002 3:57PM MetExpectations 0 0 0 1 0
4 Sep 25 2002 3:57PM overallrating 0 1 0 0 0
4 Sep 25 2002 3:57PM Prompt 1 0 0 0 0
4 Sep 25 2002 3:57PM quality 0 0 0 1 0
5 Sep 30 2002 8:11AM professionalism 0 0 0 1 0
5 Sep 30 2002 8:11AM Appearance 0 1 0 0 0
5 Sep 30 2002 8:11AM Courteous 0 0 0 1 0
5 Sep 30 2002 8:11AM MetExpectations 0 0 0 0 1
5 Sep 30 2002 8:11AM overallrating 0 0 0 1 0
5 Sep 30 2002 8:11AM Prompt 0 1 0 0 0
5 Sep 30 2002 8:11AM quality 0 0 0 1 0
6 Oct 16 2002 11:58AM professionalism 0 1 0 0 0
6 Oct 16 2002 11:58AM Appearance 0 0 0 1 0
6 Oct 16 2002 11:58AM Courteous 0 1 0 0 0
6 Oct 16 2002 11:58AM MetExpectations 0 1 0 0 0
6 Oct 16 2002 11:58AM overallrating 0 1 0 0 0
6 Oct 16 2002 11:58AM Prompt 0 1 0 0 0
6 Oct 16 2002 11:58AM quality 0 1 0 0 0
7 Oct 17 2002 7:36AM professionalism 0 1 0 0 0
7 Oct 17 2002 7:36AM Appearance 0 0 0 1 0
7 Oct 17 2002 7:36AM Courteous 0 1 0 0 0
7 Oct 17 2002 7:36AM MetExpectations 0 1 0 0 0
7 Oct 17 2002 7:36AM overallrating 0 1 0 0 0
7 Oct 17 2002 7:36AM Prompt 0 1 0 0 0
7 Oct 17 2002 7:36AM quality 0 1 0 0 0

(35 row(s) affected)

I am trying to generate some totals (sums) for each of the combinations of category (i.e. Appearance, Professionalism) and OUTSTANDING, EXCELLENT, GOOD, AVERAGE, POOR. I want to group the categories and them sum each of the columns (1s).

To do this, I want to run the following SQL statement:

SELECT category, Sum(OUTSTANDING) AS SumOfOUTSTANDING, Sum(EXCELLENT) AS SumOfEXCELLENT, Sum(GOOD) AS SumOfGOOD, Sum(AVERAGE) AS SumOfAVERAGE, Sum(POOR) AS SumOfPOOR
FROM #t
GROUP BY category


My problem is that I’m not sure how to take the results (35 rows above) generated by the stored procedure and use the SELECT SQL statement to generate a result like shown below:

My final result should look like:
category SumOfOUTSTANDING SumOfEXCELLENT SumOfGOOD SumOfAVERAGE SumOfPOOR
Appearance 0 2 0 2 1
Courteous 1 2 0 2 0
MetExpectations 1 1 0 2 1
OverallRating 1 1 0 3 0
Professionalism 1 2 0 2 0
Prompt 0 4 0 1 0
Quality 0 3 0 2 0

I tried to use another stored procedure to create a temporary table and then insert the result set (35 rows) into the temporary table. Then use the SELECT SQL statement above based on the temporary table to get the final result.

I composed the following stored procedure to insert the 35 row result set:

CREATE PROCEDURE myTempTable
AS
CREATE TABLE #myTempTable (
record numeric (9) NOT NULL,
datesubmitted datetime NOT NULL,
Category nvarchar (15) NULL,
OUTSTANDING numeric (9) NULL,
EXCELLENT numeric (9) NULL,
GOOD numeric (9) NULL,
AVERAGE numeric (9) NULL,
POOR numeric (9) NULL
)

INSERT #myTempTable
exec sp_CustSurveyData ‘original stored procedure that generated the 35 rows.



But instead of getting the final result set, I received the following error message in SQL Server 2000:


Server: Msg 8152, Level 16, State 4, Line 0
String or binary data would be truncated.

(0 row(s) affected)

What I do wrong in the process? Any helpful advice would be appreciated.
Thanks

 

[mwolf00]

Rather than create another stored procedure, you should query the temp table in sp_custSurveyData and only return the final results that you are looking for. It really doesn't make sense to have the first stored procedure only do part of the job - have it finish the job....

Of course if you want the output from the first SP as well as the output from the second, then you will probably need to create an output table in your first sp and put all data into that table. If you think that this is what you need, I can elaborate - just let me know.

 

[Cheryl3D]

mwolf00 - okay, if I'm to query the temp table in the first stored procedure (sp_custSurveyData) and only return the final results that I'm looking for, then how do I do that exactly? How do I finish the job?

You still did not address my question about the error message. Please interpret the error if you can. Why is it stating that the String or binary data would be truncated?

Also, please elaborate on the output table option.

Thanks,
Cheryl3D

 

[mwolf00]

I'd like to see the structure of the views and the tables they view.... but I think that this sp could be written like this:

CREATE PROCEDURE sp_CustSurveyData  AS

CREATE table #output(
category varchar(15),
outstanding int,
excellent int,
good int,
avrg int,
poor int
)

INSERT INTO #output 
SELECT  'Appearance', count(record)
FROM  vw_custSurv_appearance
WHERE appearance = 'OUTSTANDING'

UPDATE #output SET excellent = (SELECT count(record)
FROM vw_custSurv_appearance WHERE appearance = 'EXCELLENT')
WHERE category = 'Appearance'

UPDATE #output SET good = (SELECT count(record)
FROM vw_custSurv_appearance WHERE appearance = 'GOOD')
WHERE category = 'Appearance'

UPDATE #output SET avrg = (SELECT count(record)
FROM vw_custSurv_appearance WHERE appearance = 'AVERAGE')
WHERE category = 'Appearance'

UPDATE #output SET poor = (SELECT count(record)
FROM vw_custSurv_appearance WHERE appearance = 'POOR')
WHERE category = 'Appearance'

...

SELECT * FROM #output
GO

OR, using your code

CREATE PROCEDURE myTempTable  
AS
CREATE TABLE #myTempTable (
    record numeric (9) NOT NULL,
            datesubmitted datetime  NOT NULL,
            Category nvarchar (15) NULL,
        OUTSTANDING numeric (9) NULL,
    EXCELLENT numeric (9) NULL,
          GOOD numeric (9) NULL,
      AVERAGE numeric (9) NULL,
     POOR numeric (9) NULL
)

INSERT #myTempTable
SELECT record, datesubmitted, 'Appearance' As Category, 
CASE Appearance
 WHEN 'OUTSTANDING' THEN 1 ELSE 0 END AS OUTSTANDING,
CASE Appearance
 WHEN 'EXCELLENT' THEN 1 ELSE 0 END AS EXCELLENT,
CASE Appearance
 WHEN 'GOOD' THEN 1 ELSE 0 END AS GOOD,
CASE Appearance
 WHEN 'AVERAGE' THEN 1 ELSE 0 END AS AVERAGE,
CASE Appearance
 WHEN 'POOR' THEN 1 ELSE 0 END AS POOR
FROM vw_custsurv_appearance
Union
SELECT record, datesubmitted, 'Prompt' As Category, 
CASE Prompt
 WHEN 'OUTSTANDING' THEN 1 ELSE 0 END AS OUTSTANDING,
CASE Prompt
 WHEN 'EXCELLENT' THEN 1 ELSE 0 END AS EXCELLENT,
CASE Prompt
 WHEN 'GOOD' THEN 1 ELSE 0 END AS GOOD,
CASE Prompt
 WHEN 'AVERAGE' THEN 1 ELSE 0 END AS AVERAGE,
CASE Prompt 
WHEN 'POOR' THEN 1 ELSE 0 END AS POOR
FROM vw_custsurv_prompt
Union
SELECT record, datesubmitted, 'Courteous' As Category, 
CASE Courteous
 WHEN 'OUTSTANDING' THEN 1 ELSE 0 END AS OUTSTANDING,
CASE Courteous
 WHEN 'EXCELLENT' THEN 1 ELSE 0 END AS EXCELLENT,
CASE Courteous
 WHEN 'GOOD' THEN 1 ELSE 0 END AS GOOD,
CASE Courteous
 WHEN 'AVERAGE' THEN 1 ELSE 0 END AS AVERAGE,
CASE Courteous
WHEN 'POOR' THEN 1 ELSE 0 END AS POOR
FROM vw_custsurv_courteous
Union
SELECT record, datesubmitted,' professionalism' As Category, 
CASE Professionalism
 WHEN 'OUTSTANDING' THEN 1 ELSE 0 END AS OUTSTANDING,
CASE  Professionalism
 WHEN 'EXCELLENT' THEN 1 ELSE 0 END AS EXCELLENT,
CASE Professionalism
 WHEN 'GOOD' THEN 1 ELSE 0 END AS GOOD,
CASE  Professionalism
 WHEN 'AVERAGE' THEN 1 ELSE 0 END AS AVERAGE,
CASE  Professionalism
 WHEN 'POOR' THEN 1 ELSE 0 END AS POOR
FROM vw_custsurv_professionalism
Union
SELECT record, datesubmitted, 'MetExpectations' As Category, 
CASE MetExpectations
 WHEN 'OUTSTANDING' THEN 1 ELSE 0 END AS OUTSTANDING,
CASE MetExpectations
 WHEN 'EXCELLENT' THEN 1 ELSE 0 END AS EXCELLENT,
CASE MetExpectations
 WHEN 'GOOD' THEN 1 ELSE 0 END AS GOOD,
CASE MetExpectations
 WHEN 'AVERAGE' THEN 1 ELSE 0 END AS AVERAGE,
CASE MetExpectations
 WHEN 'POOR' THEN 1 ELSE 0 END AS POOR
FROM vw_custsurv_MetExpectation
Union
SELECT record, datesubmitted, 'overallrating' As Category, 
CASE overallrating
 WHEN 'OUTSTANDING' THEN 1 ELSE 0 END AS OUTSTANDING,
CASE overallrating
 WHEN 'EXCELLENT' THEN 1 ELSE 0 END AS EXCELLENT,
CASE overallrating
WHEN 'GOOD' THEN 1 ELSE 0 END AS GOOD,
CASE overallrating
 WHEN 'AVERAGE' THEN 1 ELSE 0 END AS AVERAGE,
CASE overallrating
 WHEN 'POOR' THEN 1 ELSE 0 END AS POOR
FROM vw_custsurv_overall
Union
SELECT record, datesubmitted, 'quality' As Category, 
CASE quality
WHEN 'OUTSTANDING' THEN 1 ELSE 0 END AS OUTSTANDING,
CASE quality
 WHEN 'EXCELLENT' THEN 1 ELSE 0 END AS EXCELLENT,
CASE quality
 WHEN 'GOOD' THEN 1 ELSE 0 END AS GOOD,
CASE quality
 WHEN 'AVERAGE' THEN 1 ELSE 0 END AS AVERAGE,
CASE quality
 WHEN 'POOR' THEN 1 ELSE 0 END AS POOR
FROM vw_custsurv_quality

SELECT category, Sum(OUTSTANDING) AS SumOfOUTSTANDING, Sum(EXCELLENT) AS SumOfEXCELLENT, Sum(GOOD) AS SumOfGOOD, Sum(AVERAGE) AS SumOfAVERAGE, Sum(POOR) AS SumOfPOOR
FROM #myTempTable GROUP BY category

-- What did you expect? This is FREE advice. LOL

 

[Cheryl3D]

Hi mwolf00:

I can't get to SQL Server until later this afternoon, in order to try out your suggestion (especially the portion using my code) but it looks like it will work. I'll let you know when I try it out.

Thanks for letting me see that I needed to create the temporary table first then use the one stored procedure (sp_CustSurveyData) to insert the results into the one temporary table.

You stated that you would like to see the structure of the views and the table that they each view. Well, here it is:

I am pulling from just one table called 'custsatisfaction'.

'custsatisfaction' table structure is:

Name Data Type Size
record numeric 9
datesubmitted varchar 30
prompt varchar 50
courteous varchar 50
quality varchar 50
professionalism varchar 50
appearance varchar 50
metexpectations varchar 50
overallratings varchar 50

The 'record' field is the primary key. (no nulls allowed)

When the table is open the rows returned look like the following: (I pasted the rows twice -- just refer to the first 1 through 5 records.)

record datesubmitted prompt courteous quality professionalism appearance metexpectations overallrating 1 10/16/2002 11:54:00AM EXCELLENT EXCELLENT EXCELLENT EXCELLENT AVERAGE EXCELLENT EXCELLENT
2 9/25/2002 2:39:00 PM AVERAGE AVERAGE EXCELLENT AVERAGE POOR AVERAGE AVERAGE
3 9/25/2002 4:15:00 PM EXCELLENT EXCELLENT AVERAGE EXCELLENT AVERAGE AVERAGE AVERAGE
4 9/30/2002 8:15:00 AM EXCELLENT AVERAGE AVERAGE AVERAGE EXCELLENT POOR AVERAGE
5 10/17/2002 7:37:00 AM EXCELLENT OUTSTANDING EXCELLENT OUTSTANDING EXCELLENT OUTSTANDING OUTSTANDING


record datesubmitted prompt courteous quality professionalism appearance metexpectations overallrating
1 10/16/2002 11:54:00 AM EXCELLENT EXCELLENT EXCELLENT EXCELLENT AVERAGE EXCELLENT EXCELLENT
2 9/25/2002 2:39:00 PM AVERAGE AVERAGE EXCELLENT AVERAGE POOR AVERAGE AVERAGE
3 9/25/2002 4:15:00 PM EXCELLENT EXCELLENT AVERAGE EXCELLENT AVERAGE AVERAGE AVERAGE
4 9/30/2002 8:15:00 AM EXCELLENT AVERAGE AVERAGE AVERAGE EXCELLENT POOR AVERAGE
5 10/17/2002 7:37:00 AM EXCELLENT OUTSTANDING EXCELLENT OUTSTANDING EXCELLENT OUTSTANDING OUTSTANDING

The structure of the views is as follows. There are 7 views, one for each category (i.e. prompt, appearance, courteous). Here is the SQL for two of the views:

SELECT custsatisfaction.record, custsatisfaction.datesubmitted,custsatisfaction.prompt
FROM custsatisfaction
GROUP BY custsatisfaction.record, custsatisfaction.datesubmitted, custsatisfaction.prompt

SELECT custsatisfaction.record, custsatisfaction.datesubmitted, custsatisfaction.appearance
FROM custsatisfaction
GROUP BY custsatisfaction.record, custsatisfaction.datesubmitted, custsatisfaction.appearance

All 7 of the views have the same structure basically. Just a few of the field names have been changed.

Hope this helps in your analysis.

Thanks,
Cheryl3D

 

[mwolf00]

I think that if you run this code, you will see that you don't need the views and the select w/ all the case statements...

CREATE table #output(
category varchar(15),
outstanding int,
excellent int,
good int,
avrg int,
poor int
)
INSERT INTO #output (category, outstanding)
SELECT  'Appearance', count(record)
FROM  custSatisfaction
WHERE appearance = 'OUTSTANDING'

UPDATE #output SET excellent = (SELECT count(record)
FROM custSatisfaction WHERE appearance = 'EXCELLENT')
WHERE category = 'Appearance'

UPDATE #output SET good = (SELECT count(record)
FROM custSatisfaction WHERE appearance = 'GOOD')
WHERE category = 'Appearance'

UPDATE #output SET avrg = (SELECT count(record)
FROM custSatisfaction WHERE appearance = 'AVERAGE')
WHERE category = 'Appearance'

UPDATE #output SET poor = (SELECT count(record)
FROM custSatisfaction WHERE appearance = 'POOR')
WHERE category = 'Appearance'


INSERT INTO #output (category, outstanding)
SELECT  'Prompt', count(record)
FROM  custSatisfaction
WHERE prompt = 'OUTSTANDING'

UPDATE #output SET excellent = (SELECT count(record)
FROM custSatisfaction WHERE prompt = 'EXCELLENT')
WHERE category = 'Prompt'

UPDATE #output SET good = (SELECT count(record)
FROM custSatisfaction WHERE prompt = 'GOOD')
WHERE category = 'Prompt'

UPDATE #output SET avrg = (SELECT count(record)
FROM custSatisfaction WHERE prompt = 'AVERAGE')
WHERE category = 'Prompt'

UPDATE #output SET poor = (SELECT count(record)
FROM custSatisfaction WHERE prompt = 'POOR')
WHERE category = 'Prompt'


INSERT INTO #output (category, outstanding)
SELECT  'Courteous', count(record)
FROM  custSatisfaction
WHERE Courteous = 'OUTSTANDING'

UPDATE #output SET excellent = (SELECT count(record)
FROM custSatisfaction WHERE Courteous = 'EXCELLENT')
WHERE category = 'Courteous'

UPDATE #output SET good = (SELECT count(record)
FROM custSatisfaction WHERE Courteous = 'GOOD')
WHERE category = 'Courteous'

UPDATE #output SET avrg = (SELECT count(record)
FROM custSatisfaction WHERE Courteous = 'AVERAGE')
WHERE category = 'Courteous'

UPDATE #output SET poor = (SELECT count(record)
FROM custSatisfaction WHERE Courteous = 'POOR')
WHERE category = 'Courteous'


INSERT INTO #output (category, outstanding)
SELECT  'Quality', count(record)
FROM  custSatisfaction
WHERE Quality = 'OUTSTANDING'

UPDATE #output SET excellent = (SELECT count(record)
FROM custSatisfaction WHERE Quality = 'EXCELLENT')
WHERE category = 'Quality'

UPDATE #output SET good = (SELECT count(record)
FROM custSatisfaction WHERE Quality = 'GOOD')
WHERE category = 'Quality'

UPDATE #output SET avrg = (SELECT count(record)
FROM custSatisfaction WHERE Quality = 'AVERAGE')
WHERE category = 'Quality'

UPDATE #output SET poor = (SELECT count(record)
FROM custSatisfaction WHERE Quality = 'POOR')
WHERE category = 'Quality'

select * from #output

I only did four categories, but I used your table definition for testing and it works great.... -- What did you expect? This is FREE advice. LOL

 

[MeanGreen]

One other quick note. When you use temp tables, the single # means that scope is LOCAL only. If you change your temp table to ## then scope becomes GLOBAL and the temp table will only go away after ALL connections are released from the temp table.

Hope this helps.

 

[Cheryl3D]

Thanks mwolf00. It worked great.

MeanGreen: In a multi-user environment is it better to change my temp table called 'output' scope from LOCAL to GLOBAL? Which is better in a multi-user environ?

Thanks

Cheryl3D

 

[MeanGreen]

The only reason you would want to change your scope to global is you want visibility outside the current connection. In other words, if you create this temp table and then have someone else connect to the database,they will not be able to see the local, but if it were global then they would have the visibility. VERY RARELY do I ever use global. Only one time I really used it, and it was to capture some data for debugging and I was using another tool (connection) to evaluate the data. But, when I was done, I wanted the table to go away, so I made it a GLOBAL temp table.

Hope this helps.

 

[Cheryl3D]

Thanks MeanGreen.

Based on your explanation, I don't think that I need to use a GLOBAL temp table in this situation.


Thanks again,
Cheryl3D

 

[ykrishnakishore]

Finally is your problem solved ?

 

[propaganda]

Hi,
I will not answer your question, I will only bring your attention that rather than creating the temp table all what you need is to:
select *
into #t
from t1

this simple SQL stmt will create the temp table #t for you exactly like the table t1.

Hope this will help.