467,149 Members | 1,362 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,149 developers. It's quick & easy.

Problem with temporary table

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
  • viewed: 1670
Share:
5 Replies
ck9663
Expert 2GB
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
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 2GB
It will be treated as one batch.

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

-- CK
Nov 13 '08 #4
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 2GB
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.

Similar topics

reply views Thread by Didier ROS | last post: by
2 posts views Thread by Jose Perez | last post: by
11 posts views Thread by randi_clausen@ins.state.il.us | last post: by
10 posts views Thread by Bing Wu | last post: by
2 posts views Thread by chettiar | last post: by
7 posts views Thread by Jon Maz | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.