Posted by Hugo Kornelis on 10/08/07 21:30

On Mon, 08 Oct 2007 02:01:58 -0700, brstowe wrote:

>On Oct 5, 7:07 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
>> brstowe wrote:
>> > How can I use multiple tables, or indeed process HUGE procedures,
>> > with
>> > dynamic table names, or temporary tables?
>>
>> Isn't this your answer right here? Just CREATE TABLE #foo instead
>> of CREATE TABLE foo, etc. and each process will get their own local
>> version of the #foo table.
>
>please be honest and tell me if I have completely misunderstood...?
>the temporary tables are session/process/user specific...
>
>two users could essentially create/drop tables, and populate/delete
>information from tables with exactly the name e.g. #temptable1 at
>exactly the same time?

Hi brstowe,

Indeed. In fact, a single user can even execute two copies of the
procedure in parallel, over different connections, and use a temp table
with the same name.

Here's a quick demo. Open two windows in SQL Server Management Studio or
Query Analyzer. Copy, paste, and execute the first code in the first
window, then (while the code is still running) copy, paste and execute
the second code in the second window.

==> Below is the code for the first query window:

-- Create temp table
CREATE TABLE #MyTempTable
(KeyColumn int IDENTITY NOT NULL PRIMARY KEY,
DataColumn varchar(30));
-- Insert some data
INSERT INTO #MyTempTable (DataColumn)
VALUES ('Data for temp table 1');
-- Show result
SELECT * FROM #MyTempTable;
-- Delay a few seconds, to allow other connnection to run
WAITFOR DELAY '0:0:10';
-- Show that temp table is unchanged
SELECT * FROM #MyTempTable;
-- Clean up
DROP TABLE #MyTempTable;
go

==> Below is the code for the second query window:

-- Create temp table - different layout than the one in the other
connection, but same name
CREATE TABLE #MyTempTable
(TheKey int IDENTITY(15,5) NOT NULL PRIMARY KEY,
TheDate datetime DEFAULT (CURRENT_TIMESTAMP),
TheText varchar(50) NOT NULL);
-- Insert some data
INSERT INTO #MyTempTable (TheText)
VALUES ('Data for temp table #2');
-- Show result
SELECT * FROM #MyTempTable;
-- Clean up
DROP TABLE #MyTempTable;
go


--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]