CODEWITHSUNDEEP
sql-servermultithreadingtemp-tables
Patrice Pezillier
Patrice Pezillier

Reputation: 4566

Temporary table in multithread

I have a multithread application.

Each thread executes store procedures in which I create a local temporary table. The name of the temporary table is the same : #TempTable

I have a conflict between thread when they manipulate this #TempTable

How can I make a #TempTable for each thread with the same name ?

Upvotes: 3

Views: 2916

Answers (3)

SWeko
SWeko

Reputation: 30892

The #TempTable temporary table should only be available to the current SQL server session/connection, so if you want each process to have a separate #TempTable, just use separate connections for each. If that is not feasible, you can use table variables, which are somewhat of a cross between regular tables and variables - like this:

DECLARE @TableVar TABLE (
  IDColumn int,
  NameColumn varchar(max))

INSERT INTO @TableVar (IDColumn, NameColumn)
SELECT  ID, Name
FROM    RealTable
WHERE   .....

If however, you want all processes to use the one and same table, just name it with double hash (##TempTable) and then it will be accessible globally.

Upvotes: 0

Oded
Oded

Reputation: 499002

You need to ensure each thread is running in a different connection from the connectio npool (so it is in a different session).

Temp tables are only visible in their own session.

From MSDN:

Local temporary tables are visible only in the current session

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

So long as the multiple threads are using separate connections (which I really hope they are, otherwise there's probably no benefit to multithreading, or you have a massive race condition hiding in your code), they should all have their own copies of the temp table. You shouldn't have to do anything else.

Upvotes: 1

Related Questions