Create multiple temp tables

  • TomT

    SSC Eights!

    Points: 887

    Hi. Is it possible to create multiple, unique temp tables, per user? E.g., I have a report which collects a fair bit of information based on a number of parameters selected at run time. I'd like to be able to create a temp table based on those selections, upon which to base the report.

    Multiple users could be running this at the same time, with different parameters, so each would need their own copy of the data, stored in a separate temp table.

    Right now, I bring this temp data back to the client system into a temp table there, but I would be interested to know if I can keep it all on the server side in temp tables.

    Hope this makes sense,

    Thanks

  • Crispin Proctor

    SSCoach

    Points: 18161

    A temp table lives and is unique for each connection. When the query goes out of scope SQL cleans up after you. (Deletes any temp tables.)

    If you want the temp table to live longer, use ##Temp as this lives for the live of the connection and is unique per connection ID.

    When SQL creates a temp table, it adds a random amount of underscores for each connection.

    Not sure if this is what you asking. 🙂

    Cheers,

    Crispin




    Cheers,
    Crispin


    I can't die, there are too many people who still have to meet me!

    It's not a bug, SQL just misunderstood me!

  • TomT

    SSC Eights!

    Points: 887

    Crispin, thanks. Is there any way to return the name of each of these tables, per user? E.g., if user A created a temp table (the longer life type), how would I be able to reference it in a select statement for example?

    Thanks

  • Crispin Proctor

    SSCoach

    Points: 18161

    Best way I can see. Must be a cleaner way. I would like to know if anyone does know a better way.

     
    
    ALTER Procedure CPTest
    as

    If Not Exists(Select * From TempDB..SysObjects Where Name = '##Client')
    Begin
    Create Table ##Client(Name VARCHAR(10))
    Print 'Table created'
    -- Do some other work here to fill the table.
    End

    Select * From ##Client


    -- This will create a temp table that lives for the life of the connection.
    --Not sure if there's a cleaner way to do it. I would like to find out.

    Cheers,

    Crispin




    Cheers,
    Crispin


    I can't die, there are too many people who still have to meet me!

    It's not a bug, SQL just misunderstood me!

  • jpipes

    SSChampion

    Points: 12230

    Crispin,

    I think I understand the ## designation differently. As far as I understand, putting ## before the table name designates that, for the life of the instantiating connection, other connections have access to the temp table's data. However, once the initial connection goes out of scope, or drops its table manually, the data is lost.

    As for SQL "cleaning up after you", not quite the case...Everything is done in tempdb, so it will depend on when your tempdb transaction log truncates....

    Jay

  • G.R.Prithiviraj Kulasingham

    SSCarpal Tunnel

    Points: 4104

    I use a different method.

    I use one temporary table with an additional field (Session_ID)

    This Session ID isgenerated by the application.

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • G.R.Prithiviraj Kulasingham

    SSCarpal Tunnel

    Points: 4104

    I use a different method.

    I use one temporary table with an additional field (Session_ID)

    This Session ID isgenerated by the application.

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • Crispin Proctor

    SSCoach

    Points: 18161

    quote:


    Crispin,

    I think I understand the ## designation differently. As far as I understand, putting ## before the table name designates that, for the life of the instantiating connection, other connections have access to the temp table's data. However, once the initial connection goes out of scope, or drops its table manually, the data is lost.

    As for SQL "cleaning up after you", not quite the case...Everything is done in tempdb, so it will depend on when your tempdb transaction log truncates....

    Jay


    This is the thing I like about news groups. Things I assumed are corrected by others.

    I stand corrected... Thanks!

    Cheers,

    Crispin




    Cheers,
    Crispin


    I can't die, there are too many people who still have to meet me!

    It's not a bug, SQL just misunderstood me!

  • cliffb

    SSCarpal Tunnel

    Points: 4547

    I would be inclined to agree with Preethi.

    Set up a table that holds a sessionid or some other type of unique identifier and one column for each parameter.

    The other option would be to use a name/value pair kind of architecture where you would have a sessionid,ParamName,ParamValue table. I'd throw in a DateCreated field as well. That way you could write a cleanup script based on the date to keep the table from getting out of control.

    You could then reference this table whenever that user requested a report until they changed thier parameters? Just a thought.

    Edited by - cliffb on 06/18/2003 06:31:17 AM

  • beachldy

    SSC Enthusiast

    Points: 198

    I use Access for a front end and Sql for a backeend. Often, I need to create temp tables for the individual user in order to connect the linked temp table temporarily. I used it mostly for complex crosstabs that Access cannot do. And I have to make sure the table is unique so I name the table something like "_TempForPerm" and assign the user's logon to the table name so it would be "FIN/MDS_TempforPerm"

    In access, I get the user's name from their logon (NT or Access)...and pass those parms to the stored procedure. Here's a stored procedure that does this. I drop the table from another SP when the user ends the process:

    CREATE PROCEDURE [sp_UserSpecProcRestraintDebrief] (@tbl nvarchar (200), @usr nvarchar (100), @own nvarchar(100), @ctr nvarchar(10))

    AS

    /*@tbl is the user table assigned by a variable in the front end*/

    /*DROP USER SPECIFIC TABLE FIRST*/

    DECLARE @1 varchar(100)

    DECLARE @2 varchar (100)

    DECLARE @3 varchar(100)

    DECLARE @4 varchar (100)

    DECLARE @5 varchar (100)

    DECLARE @s1 varchar(100)

    DECLARE @s2 varchar(200)

    DECLARE @strsql varchar(1500)

    SET @1 = 'if exists (select * from sysobjects where id = object_id(N'''

    SET @2 = '[' + @own + '].[' + @tbl + ']' + ''''

    SET @3 = ') and OBJECTPROPERTY(id,N' + ''''

    SET @4 = 'IsUserTable' + ''''

    SET @5 = ') = 1) Drop Table [' + @own + '].[' + @tbl + ']'

    EXEC (@1 + @2 + @3 + @4 + @5)

    /*CREATE new user table*/

    Exec('Create Table ' + @tbl + '(

    SurveyCounter numeric NOT NULL,

    Section nvarchar(50) NOT NULL,

    QuestionNr numeric NOT NULL,

    Question text,

    Points numeric,

    Rating text,

    Heading nvarchar(100))')

    --CREATE INDEX

    EXEC ('ALTER TABLE ' + @tbl + ' WITH NOCHECK ADD CONSTRAINT [' + @tbl + @usr + '] PRIMARY KEY NONCLUSTERED

    (

    [SurveyCounter],

    [Section],

    [QuestionNr]

    ) ON [PRIMARY] ')

    --INSERT RECORDS(Staff Section)

    Set @strsql = 'INSERT INTO [' + @tbl +

    + '] ( SurveyCounter, [Section], [QuestionNr], Question, Points, Rating, Heading )

    SELECT a.SurveyCounter, "Staff Section" AS Type,

    b.QuestionNumber, b.Question,

    a.QPoints1, C.RatingDescription,

    b.Section

    FROM (SpecProc_RestraintDebrief_ReviewDETAIL a

    LEFT JOIN SpecProc_RestraintDebriefing_ReviewQuestions b

    ON a.Q1 = b.QuestionID)

    LEFT JOIN SpecProc_RestraintDebriefing_ReviewRatings c

    ON a.QPoints1 = c.RatingNumber

    WHERE a.surveycounter = ' + @ctr

    Exec (@strsql)

  • TomT

    SSC Eights!

    Points: 887

    Great suggestions/feedback. I really appreciate everyone's input on this.

    beachidly, I also use an Access front end. I assume in your process you link the newly created table to the front end, after you've created and populated it....

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply