By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,295 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Problem with temporary table

P: 7
Hello,

I'm trying to execute the following query on SQL Server 2000:

SELECT *
INTO #temp
FROM table1

drop table #temp

SELECT *
INTO #temp
FROM table2

The above does not work. An error occurs, according to which the table #temp cannot be created beacause it already exists.

But why is that? Why the "drop table" statement doesn't execute before the 2nd select statement which creates the same table as the first select statement?

Please, is there a way to make this work? And please don't tell me to use different temporary tables or to execute each statement in the query separately. It is important to use the same table and execute the query as whole, the above query is just a simplified example of what I really want to do.

Thanks in advance for your time.
Nov 12 '08 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
The parser did the check for you. You created the same temp table within the same batch.

If you're insisting on doing it, try:

Expand|Select|Wrap|Line Numbers
  1.  
  2. select * into #temp1 from table1
  3.  
  4. drop table #temp
  5. go
  6.  
  7. select * into #temp from table2
  8.  
  9. drop table #temp
  10.  
  11.  

-- CK
Nov 12 '08 #2

P: 7
Thanks for the help

I've got one more question. What if I have the following query:

Expand|Select|Wrap|Line Numbers
  1. declare @var int
  2.  
  3. set @var=1
  4.  
  5. while @var<=5
  6. begin
  7.   select *
  8.   into #temp
  9.   from table
  10.   where table.field=@var
  11.  
  12.   set @var=@var+1
  13.  
  14.   drop table #temp
  15. end
  16.  
Is this going to work? Or is #temp considered to be created more than once in the same batch?
Nov 13 '08 #3

ck9663
Expert 2.5K+
P: 2,878
It will be treated as one batch.

What are you trying to do? Maybe a table variable will do.

-- CK
Nov 13 '08 #4

P: 7
How should the above code (with the while statement) be written using a variable table?

Are variable tables temporary? That is, they are not actually added in the database?

Thanks in advance.
Nov 13 '08 #5

ck9663
Expert 2.5K+
P: 2,878
Read more about table variable here

-- CK
Nov 13 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.