Building multiple "temp" tables in SQL....

Hello,
Using SQL 2000 and I want to write a query that creates a different "temp"
table every time the query is run so that more than one user cannot be
accessing the same temp table at the same time.
I see two possible ways of doing this.
1.) My app can take the user ID as an input field, and I suppose that I
could use this in the query (as a variable) to generate a separate "temp "
table for each user based on their ID.
For example, I'm Joe and my user ID is 1.
In the app I select my user ID as an input field and I click a button. The
button runs the query which needs to take that User ID and create a temp
table with the ID as part of the table name so that it is different for each
user.
2.) If I can just tell the query to create a differentiated "temp" table
every time it is run (say with an increment function or something) that woul
d
work as well.
My goal is simply to make sure that when the query is run that not more than
one person is accessing the same temp table at the same time.
The table is then truncated or dropped by another process.
Any idea if of how this can be done?
Thanks much,
Markhi
may be you should spend some time to understand what temp tables are, read
BOL and
http://www.sqlteam.com/article/temporary-tables
http://www.sql-server-performance.c...ived_tables.asp
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
> Hello,
> Using SQL 2000 and I want to write a query that creates a different "temp"
> table every time the query is run so that more than one user cannot be
> accessing the same temp table at the same time.
> I see two possible ways of doing this.
> 1.) My app can take the user ID as an input field, and I suppose that I
> could use this in the query (as a variable) to generate a separate "temp "
> table for each user based on their ID.
> For example, I'm Joe and my user ID is 1.
> In the app I select my user ID as an input field and I click a button.
> The
> button runs the query which needs to take that User ID and create a temp
> table with the ID as part of the table name so that it is different for
> each
> user.
> 2.) If I can just tell the query to create a differentiated "temp" table
> every time it is run (say with an increment function or something) that
> would
> work as well.
> My goal is simply to make sure that when the query is run that not more
> than
> one person is accessing the same temp table at the same time.
> The table is then truncated or dropped by another process.
> Any idea if of how this can be done?
> Thanks much,
> Mark
>|||Key point: temp tables cannot be access by anyone outside of the current
connection (unless they are global temp tables). Thus you can use the exact
same name (such as #tmp) for every execution and have no worries that they
will step on each other's data.
TheSQLGuru
President
Indicium Resources, Inc.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> hi
> may be you should spend some time to understand what temp tables are,
> read BOL and
> http://www.sqlteam.com/article/temporary-tables
> http://www.sql-server-performance.c...ived_tables.asp
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>|||Hello,
Point well taken, however I seem to have another problem. It appears that
temp talbes are destroyed after the process is finished? I need my temp
table to "stick around" for a few other queries or SP's to use it, only then
do I need to get rid of it.
Can I somehow just create a separate regular table that can be given a
different name for each user that runs the Sp or SQL code?
Thanks much,
Mark
"TheSQLGuru" wrote:

> Key point: temp tables cannot be access by anyone outside of the current
> connection (unless they are global temp tables). Thus you can use the exa
ct
> same name (such as #tmp) for every execution and have no worries that they
> will step on each other's data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>
>|||If you need persisted data, temp tables aren't the way to go.
Yes, you CAN create permanent tables that are uniquely named using the
login/dbuser name and perhaps some counter obtained from a sequence table.
Or you could use a GUID for the name. But how would you know what table to
access for follow-on queries/sprocs? If you pass table name as variable -
you are stuck with dynamic sql. Also, how would you clean these objects up?
Are you SURE you must have this type of processing in place'
TheSQLGuru
President
Indicium Resources, Inc.
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...[vbcol=seagreen]
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>|||If you create normal/regular table with name like <user><data code>, same
user will be able to use it. Even after disconnect.
Did you try to create named tables in tempdb? This could solve your issue
too. Just remember they will be kept between sessions, so you'll need to
manage them too.
HTH
Alex
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>
>