?
Solved

Best way to manage temp table

Posted on 2006-04-27
21
Medium Priority
?
244 Views
Last Modified: 2013-12-03
What is the best way to manage this.  Basically below is one stored procedure which at the end, calls another and insert it's data into a temp table so that I can do a union on the information between the 2 stored procs.  What is the best way to manage this as in clearing the data each time the insert happens into the temp table.  Should I delete and recreate the temp table each time or just delete the records.  I haven't dealt much with temp tables so not sure the best way to clear it every time the main stored proc is called.  This proc is being used in a report in SSRS 2005.  I'm not sure of the life span of the temp table...I assume it stays there until you delete it just like a normal table.

alter mainstoredproc
....
...main stored proc's here SQL, and then at the end I have the insert and call of another stored proc below.  I need to first clear any previous data
at this point in #temp before the insert on the next line

INSERT INTO #temp
EXEC SSRS_Get_CurrentMonthCollections_IL

SELECT      ProjFee,
            ProjGross,
            DailyRunRate,
            Var1,
            InHouse1,
            InHouse2,
            GrossGoal,
            'Illinois',
            PostedAmount

FROM #temp

UNION

SELECT      ProjFee = @ProjFee,
            ProjGross = @ProjGross,
            DailyRunRate = @DailyRunRate,
            Var1 = @Var1,
            InHouse1 = @InHouse1,
            InHouse2 = @InHouse2,
            GrossGoal = @GrossGoal,
            GroupName = 'Arizona',
            PostedAmount = @Posted
0
Comment
Question by:dba123
  • 11
  • 6
  • 2
  • +1
21 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1600 total points
ID: 16556401
dba123,
> Should I delete and recreate the temp table each time or just delete the records.

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP #temp

CREATE TABLE #Temp

INSERT INTO #temp
EXEC SSRS_Get_CurrentMonthCollections_IL

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP #temp

END -- End of Sp
0
 
LVL 5

Assisted Solution

by:morisce
morisce earned 400 total points
ID: 16556515
> I'm not sure of the life span of the temp table...I assume it stays there until you delete it just like a normal table.
=> temporary table is completely cleared (both structure and data) at the end of the session.
=> So, it is necessary to drop / create the temporary table
0
 
LVL 1

Author Comment

by:dba123
ID: 16556581
so if it's cleared afer the end of the session, do you mean session as in Query Analyzer or end of the sql statement run?  If it's at the end of the QE session then I see that yes, I'd have to recreate the table...
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 1

Author Comment

by:dba123
ID: 16556584
what is the performance hit on having to drop and recreate a temp table that is very small?
0
 
LVL 1

Author Comment

by:dba123
ID: 16556595
I wouldn't want to delete it after the sp is done because my SSRS report needs to reference that data...or maybe that's fine because once the data is passed to SSRS, it caches it and it doesn't matter that I've deleted the temp table right after the UNION?

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP #temp

CREATE TABLE #Temp

INSERT INTO #temp
EXEC SSRS_Get_CurrentMonthCollections_IL

SELECT      ProjFee,
            ProjGross,
            DailyRunRate,
            Var1,
            InHouse1,
            InHouse2,
            GrossGoal,
            'Illinois',
            PostedAmount

FROM #temp
0
 
LVL 5

Expert Comment

by:morisce
ID: 16556720
creating temporary table is  faster than creating regulary table because the server don't manage a log system in the temporary space.
for using with SSRS : let the server cleaning temporary table if you have a short sessions
0
 
LVL 1

Author Comment

by:dba123
ID: 16556795
Sorry, this sentence isn't clear to me:

for using with SSRS : let the server cleaning temporary table if you have a short sessions
0
 
LVL 5

Expert Comment

by:morisce
ID: 16556832
create and populate your temporary table without dropping it. At the end of the session the table will be cleaned automatically by the server.
0
 
LVL 1

Author Comment

by:dba123
ID: 16556926
again, what do you mean by session?
0
 
LVL 5

Expert Comment

by:morisce
ID: 16556972
session = connection
0
 
LVL 1

Author Comment

by:dba123
ID: 16558745
connection to what?  I'm running this in sql analyzer right now.  So the temp table doesn't clear till what, the sql ends?  I'm not talking the ui level..
0
 
LVL 35

Expert Comment

by:James0628
ID: 16559676
If the table is fairly small, you might want to use a table variable instead.  Table variables are supposed to be more efficient (if they're not too large).  The table variable will automatically disappear as soon as the procedure that created it ends (like other variables).

 James
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16559686
>you might want to use a table variable instead
But in this case the usage of table variables is not possible, since he actually wants his result to be inserted to a table..

INSERT INTO #temp
EXEC SSRS_Get_CurrentMonthCollections_IL
0
 
LVL 35

Expert Comment

by:James0628
ID: 16560082
> But in this case the usage of table variables is not possible ...

 You may be right.  I assumed that there was a way to get the output from an executed stored procedure into a table variable (apart from something like using a temp table and then copying that to the table variable, which would kind of defeat the purpose), but I don't think I've ever tried it.

 James
0
 
LVL 5

Expert Comment

by:morisce
ID: 16560480
session is the tiemelife between a new connection (log on) and the disconnect action (log  off).
the connection is not necessary a "ui" log on, it can be the action of any application using the database (asp pages, crystal reports, ...)
0
 
LVL 1

Author Comment

by:dba123
ID: 16563659
cool, thanks morisce for clarifiying session as in non-ui, I figured that it was Query Analyzer's session in this case...since I'm not talking about the UI's session management.
0
 
LVL 1

Author Comment

by:dba123
ID: 16563664
basically all I care about is retrieving the data, whether that's through a table ,etc. doesn't matter to me.  The reason I'm doing an insert is because it was suggested as the only way to also include the results from running another stored procedure inside an existing stored procedure situation.
0
 
LVL 1

Author Comment

by:dba123
ID: 16563693
I'm not doing this right

IF OBJECT_ID('dbname.temp') IS NOT NULL
DROP #temp
0
 
LVL 1

Author Comment

by:dba123
ID: 16563952
Ok, here's my code with some more errors:

Msg 156, Level 15, State 1, Procedure SSRS_Get_CurrentMonthCollections_AZ, Line 518
Incorrect syntax near the keyword 'CREATE'.
Msg 156, Level 15, State 1, Procedure SSRS_Get_CurrentMonthCollections_AZ, Line 520
Incorrect syntax near the keyword 'INSERT'.

IF OBJECT_ID('servername.#CurrentMonthCollections_IL') IS NOT NULL
DROP #CurrentMonthCollections_IL

CREATE TABLE #CurrentMonthCollections_IL

INSERT INTO #CurrentMonthCollections_IL
EXEC SSRS_Get_CurrentMonthCollections_IL

SELECT      ProjFee,
            ProjGross,
            DailyRunRate,
            Var1,
            InHouse1,
            InHouse2,
            GrossGoal,
            'Illinois',
            PostedAmount

FROM #CurrentMonthCollections_IL

UNION

SELECT      ProjFee = @ProjFee,
            ProjGross = @ProjGross,
            DailyRunRate = @DailyRunRate,
            Var1 = @Var1,
            InHouse1 = @InHouse1,
            InHouse2 = @InHouse2,
            GrossGoal = @GrossGoal,
            GroupName = 'Arizona',
            PostedAmount = @Posted

END
0
 
LVL 5

Expert Comment

by:morisce
ID: 16563978
>> CREATE TABLE #CurrentMonthCollections_IL

You must put the table definition : columns and types
CREATE TABLE #CurrentMonthCollections_IL (COL1 type1, COL2 type2, ...)
0
 
LVL 1

Author Comment

by:dba123
ID: 16563995
Ok, figured that.  I assumed though that it could take the params from the called sp and create the fields based on the param type....guess not.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question