0

I am running a big query that creates a lot of temp tables. For each one, I put

if object_id('tempdb..##TempTable1') is not null
drop table ##TempTable1;

at the very beginning before I "select" variables "into" the temp table. But sometimes I still got error message that says "##TempTable1" already exists. This seems kind of random; it happens to some other temp tables as well. Why would this problem happen? Is there a better way to avoid this issue?

JasonSmith
  • 499
  • 1
  • 6
  • 8
  • 4
    Those are global temp tables. That means they are available to any connection. If this is inside a stored procedure I would recommend using temp tables instead. Change your declaration to use a single # instead of ##. Global temp tables are really dangerous if you are not 100% certain what you are doing with them. – Sean Lange Aug 22 '14 at 15:51
  • you really need a global temp ##Table instead of local one? – Hlin Aug 22 '14 at 15:51
  • Why do you need `lot of temp tables`? – Anup Agrawal Aug 22 '14 at 15:52
  • 1
    Sean nailed it. The intermittent issue is likely due to another connection using that global temp table. In fact, you may be dropping that global temp table on someone else with this code. Unless you need to share a temp table between connections (which is very unlikely) use `#TempTable1` instead of `##TempTable1`. – Mike Aug 22 '14 at 15:54
  • Thank you all for the comments. I guess the reason why the query creates so many temp tables is to take a small step at each time so it won't take too much resources on server and affect other users. The temp tables are huge, which must be stored on the server's temp storage. I am not sure changing the table from global to local will move it to the local storage or just change the access. – JasonSmith Aug 22 '14 at 20:34
  • local refers to the scope of the table. a local table exists for your connection but not for the others and it will automatically dissapear completely when your connection is finished. Under the bonnet the a seperate table to serve as #temptable will be created in tempdb with a unique name to avoid interfering with other processes – Tom Page Aug 22 '14 at 21:15
  • http://stackoverflow.com/questions/2920836/local-and-global-temporary-tables-in-sql-server – Tom Page Aug 22 '14 at 21:17

0 Answers0