asked on

Best way to manage temp table

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

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

morisce

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dba123

ASKER

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...

dba123

ASKER

what is the performance hit on having to drop and recreate a temp table that is very small?

dba123

ASKER

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

morisce

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

dba123

ASKER

Sorry, this sentence isn't clear to me:

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

morisce

create and populate your temporary table without dropping it. At the end of the session the table will be cleaned automatically by the server.

dba123

ASKER

again, what do you mean by session?

morisce

session = connection

dba123

ASKER

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..

James0628

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

Aneesh

>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

James0628

> 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

morisce

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, ...)

dba123

ASKER

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.

dba123

ASKER

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.

dba123

ASKER

I'm not doing this right

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

dba123

ASKER

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

morisce

>> CREATE TABLE #CurrentMonthCollections_IL

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

dba123

ASKER

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.