Re: Using temp tables

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: avnrao (avn_at_newsgroups.com)
Date: 01/06/05 

Date: Thu, 6 Jan 2005 16:48:12 +0530

Hi Roy,

If you need the result set of stored procedure during the execution of
procedure that calls, you can store the data in Temporary tables or Physical
tables.

The source for information on temp tables is - BOL- Books online is the best
place. Look for Temporary Tables in the index.

You can store the result set as

Create Table #MyResultSet (Column Int.) -- Generally structure of this
should be same as output of stored procedure.
Insert Into #MyResultSet Exec Stored Proc --Here Temp table #MyResultSet
should be defined earlier

There are two types of Temp tables. local and global. local temp tables are
created as #TableName, and global are created as ##GlobalTableName.
The difference is local temp tables are visible only to the execution.
Meaning if you call a stored proc that creates a temp table, the temp table
is visible to the execution of this proc, or in any procs called by the main
proc. Global temp table is visible to other processes. it exists even after
its creator's execution gets completed.

> By that time this dable should be watching on all the network.

what does this mean? 

-- 
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Roy Goldhammer" <roygoldh@hotmail.com> wrote in message
news:e7LqTw98EHA.3840@tk2msftngp13.phx.gbl...
> Hello there
>
> I have application on sql server 2000
>
> In the application i need sometimes to store result of store procedures or
> other things into tables for some time and destroy it afterword. By that
> time this dable should be watching on all the network.
>
> As I understand i should use temp tables for this. The question is how can
i
> create them, use them outside and destroy them?
>
> any help would be useful
>
>

Relevant Pages

  • Storing preprocessed data over executions
    ... This is a text file containing descriptions of linguistic trees. ... So I want to store the preprocessed tree banks somewhere in a directory for my program. ... Furthermore, I’d like to have access to them in a subsequent execution of the program, since this preprocessing step is quite intensive and there is no need to do it each time. ... What I am trying now is to create a temp dir and store stuff there, but that does not persist between executions, since a new temp dir is created each time. ...
    (comp.lang.java.programmer)
  • Re: SQL server 2005 Express - Vista - Invalid object name #spdbdesc
    ... #tableName refers to a temp table-- does it exist when you're doing this? ... the sproc execution automagically ... Used server manager to create user login with sysadmin role. ... my application executes the stored procedure "sp_helpdb" ...
    (microsoft.public.sqlserver.msde)
  • Re: Insert additional value then the number of rows returned
    ... Tibor Karaszi, SQL Server MVP ... The temp table would have all the columns that the stored procedure returns ... EXEC <Store Procedure> ...
    (microsoft.public.sqlserver.programming)
  • Re: Temporary results used by SP
    ... You can create the temp table prior to the execution of the stored procedure and have the sp insert ... > b) use the data in "xyz" to do something ...
    (microsoft.public.sqlserver.programming)
  • Re: Insert additional value then the number of rows returned
    ... Tibor Karaszi, SQL Server MVP ... The temp table would have all the columns that the stored procedure ... EXEC <Store Procedure> ...
    (microsoft.public.sqlserver.programming)