Temp Table question

[blar9]

I have a query that creates a temp table like the query below.

Select * INTO tmpTable FROM Table1 WHERE...

Then I do a join on the tmpTable

Select * from Table2 inner join tmpTable on .....

Then I drop the tmpTable

drop table tmpTable

This works fine but I want to know what happens if 1 the tmpTable doesn't get dropped or if that is possible and 2 what if someone else queries this at the same time will it crash on the create tmpTable?

Thanks

 

[AlexCuse]

Try using a table variable instead. It is a little more work as you need to declare it like so:

declare @tbl table (col1 int, col2 varchar(50))

Then populate it:

insert into @tbl
select col1, col2 from table1
where something = something

But, they are faster for most purposes because they are only created in memory, and there is no need to worry about dropping them!

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Gixonita]

Hello,

the outcome depends on 3 things:

A) Your tmpTable is a not really a temp table and you are just using the "tmp" name. ("Normal" table)

B) Your tmpTabl is a #tmpTable ("Session" table)

C) your tmpTabl is a ##tmpTable ("Global Session" table)

A) You have to make sure you drop the table at the end of your process. Anyone can query it and Everyone will have a problem running the query since its going to try to create a table with the same name.

B) You can either drop the table using the DROP command or wait until your session ends and it will be dropped automatically. No one can see the table but you and ANYONE can run the query since the table belongs to the user's session.

C) You can either drop the table using the DROP command or wait until your session ends and it will be dropped automatically. Anyone can query the table and Everyone will have a problem running the query since its going to try to create a table with the same name.

Hope this helps

 

[nice95gle]

what Alex say is def true but if you must go with a temp table you can check it's existence by using the following

IF OBJECT_ID('#tmpTable') IS NOT NULL 
	DROP TABLE #tmpTable

This will drop the table before you try to create it again.





Well Done is better than well said
- Ben Franklin