Results 1 to 7 of 7

Thread: #temp query

  1. #1
    Join Date
    Oct 2005
    Posts
    58

    Unanswered: #temp query

    I have a very long stored procedure running with a temp table in it. While the stored procedure is running (and the temp table hasn't been dropped) How can I query the temp table.

    I've tried something like the following with no success:

    Select * from tempdb..#tempTable

  2. #2
    Join Date
    Aug 2004
    Posts
    76
    u can try these options

    select * from #tempTable

    select * from tempdb.#tempTable

  3. #3
    Join Date
    Jun 2005
    Posts
    85
    From BOL :

    If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.

    Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

    * A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.

    * All other local temporary tables are dropped automatically at the end of the current session.

    * Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

    ----------------------------

    Hence try using ## (global temp tables), if you need to query outside the stored procedure.
    Last edited by MiraJasmin; 12-05-05 at 02:15.

  4. #4
    Join Date
    Oct 2005
    Posts
    58
    Can you give me an example in a select statement. The temporary table shows up in tempdb sysobjects as:

    #Dup______________________________________________ __________________________________________________ ________________00000000001A

  5. #5
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    example

    Code:
    use pubs
    go
    select * into ##temp from authors
    select * from ##temp
    --or
    select * from tempdb.##temp
    --or
    select * from tempdb..##temp
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  6. #6
    Join Date
    Oct 2005
    Posts
    58
    I get "invalid object name" trying any of these. I even tried it straight from the tempdb.

  7. #7
    Join Date
    Jun 2005
    Posts
    85
    Give your stored procedure which creates the temp table and explain how you have queried the same outside the procedure.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •