• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

creating #temp table throwing error

Hi All,

I am trying to create a temp table based on condition. However i am receiving error. I have two temp tables named #temp1 and #temp2. This is basically to do a search.

I fetch all required data into #temp1 initially.  
if exists(select name from tempdb.sys.tables where [name] like '#Temp2%')
begin
drop table #temp2
end

if (searchterm is integer)
begin
select records from #temp1 into #temp2 for searchterm --simplified here , query is more complex
end

else

begin
if exists(select name from tempdb.sys.tables where [name] like '#Temp2%')
begin
drop table #temp2
end
select records from #temp1 into #temp2 for searchterm 
end

Open in new window


I am using a select into to create #temp2. I cannot change name #temp2. When i execute the above i receive a error as below

"There is already an object named #Temp" in the database".

Any help is appreciated.
0
Jyozeal
Asked:
Jyozeal
  • 3
  • 2
2 Solutions
 
Mike EghtebasDatabase and Application DeveloperCommented:
At line 1 and 14, remove % from  '#Temp2%')

use

if exists(select name from tempdb.sys.tables where [name] like '#Temp2')

or try:
if exists(select name from tempdb.sys.tables where [name] like '#Temp2' + '%')
begin
drop table #temp2
end

if (searchterm is integer)
begin
select records from #temp1 into #temp2 for searchterm --simplified here , query is more complex
end

else

begin
if exists(select name from tempdb.sys.tables where [name] like like '#Temp2' + '%')
begin
drop table #temp2
end
select records from #temp1 into #temp2 for searchterm 
end

Open in new window


or just:
if exists(select name from tempdb.sys.tables where [name] ] = [#Temp2])
begin
drop table #temp2
end

if (searchterm is integer)
begin
select records from #temp1 into #temp2 for searchterm --simplified here , query is more complex
end

else

begin
if exists(select name from tempdb.sys.tables where [name]  = [#Temp2])
begin
drop table #temp2
end
select records from #temp1 into #temp2 for searchterm 
end

Open in new window

0
 
JyozealAuthor Commented:
eghtebas, thanks for your reply. I tried above options still same error. When i do a select into it creates a new temp table . However when select into is encountered again in else is sql server assuming that this object might already exist?

since i am doing it in if else only one of them gets executed at point of time.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
How about using something like:

if (searchterm is integer)
begin
   if exists(select name from tempdb.sys.tables where [name] ] = [#Temp2])
     begin
        delete from #temp2
        Inset  #temp2 Select * From #temp1;
    end
 else
   select records from #temp1 into #temp2 for searchterm --simplified here , query is more complex
end

Open in new window


Provided the line below doesn't include an identity column.
Inset  #temp2 Select * From #temp1;
0
 
JyozealAuthor Commented:
I used CTE s and insert into to get around the issue. Thanks for all the help.
0
 
JyozealAuthor Commented:
I used CTE s and insert into to get around the issue
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now