Create multiple temp tables

Question

Create multiple temp tables

TomT

SSC Eights!

Points: 887
More actions
June 17, 2003 at 5:49 am

#160382

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

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

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

Crispin Proctor SSCoach Points: 18161 More actions · Answer 1

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,CrispinI 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 More actions · Answer 2

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 More actions · Answer 3

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,CrispinI 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 More actions · Answer 4

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: 4105 More actions · Answer 5

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: 4105 More actions · Answer 6

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 More actions · Answer 7

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,CrispinI 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: 4598 More actions · Answer 8

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 Veteran Points: 278 More actions · Answer 9

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,

QuestionNrnumeric NOT NULL,

Questiontext,

Pointsnumeric,

Ratingtext,

Headingnvarchar(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 More actions · Answer 10

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