Temp table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Temp table

Author

Topic

misterraj
Yak Posting Veteran

94 Posts

Posted - 2004-12-22 : 01:44:15

HI,
I create a temperory table in a Stored procedure. This runs for a cosiderable amount of time. I am inserting a one row into this temp table.

Now consider that some other user is also executing this stored procedure(It is possible that the application may be executed simultaneously). Does the temp table inserts records twice or only once?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-22 : 01:48:11

quote:
Originally posted by misterraj

HI,
I create a temperory table in a Stored procedure. This runs for a cosiderable amount of time. I am inserting a one row into this temp table.

Now consider that some other user is also executing this stored procedure(It is possible that the application may be executed simultaneously). Does the temp table inserts records twice or only once?

is the temporary table global or local to the sproc?

if it's global then, possible error is, the table already exists and creating it will fail the succeeding transactions within sp (if not handled else, inserts will be committed)

--------------------
keeping it simple...

misterraj
Yak Posting Veteran

94 Posts

Posted - 2004-12-22 : 03:15:47

The temperory table is not global.

if inserts are committed then how do i handle that? At any point of time I need only one record to be proceessed and updated.

application1 ----> Initiates Stored proc1(this has temp table #temp1 and this inserts the record) and this SP is still in process,

mean while if another instance of the application initiates the same storedproc and if it tries to insert a record in the temp table, does 2 records get inserted?

I donot want this....since I may have updates in the first instance which then updates all the rows, including the second insertion.

how do i handle this??

pls comment.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-12-22 : 03:44:21

Temp tables are "connection" specific so if 2 "users" execute the same stored procedure then each will have their own temp table

Unless of course as jen said it is a global temp table

You can test this in QA
SELECT * INTO #Temp
FROM Orders

SELECT * FROM #Temp

Then go to new query and try
SELECT * FROM #Temp

You will get an error

If you go back to your original window and do
SELECT * FROM #Temp
You will still see the data in the temp table

Andy

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-22 : 23:46:02

just an idea,

you could set a table (containing status) to monitor if the sproc is being used or not. if it's being used, then do not call or prompt the user to try again later since the procedure is being used, then update the table.status to available if the transaction is complete.

otherwise, you could just set lock options in your application layer's connection object when calling the procedure just in case the same row is being updated.

--------------------
keeping it simple...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-23 : 02:02:14

Read AndyB13's post.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources