There is already an object named '#Temp' in the database,,related to Temp table

Question

0.00/5 (No votes)

See more:

C#

ASP.NET

SQL-Server

, +

Hi,

I am facing problem related to Temporary table...

I have create Storead procedure.
below is my scenario in SP.

SQL

IF OBJECT_ID('tempdb..#temp' ) IS NOT NULL DROP TABLE #temp;
IF OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1;
IF OBJECT_ID('tempdb..#comman') IS NOT NULL DROP TABLE #comman ;

 If type ='A'
    BEGIN
       select abc,d,e,f into #temp from mst_temp
    END
 ELSE
    BEGIN
      select abc,d,temp1 into #temp1 from mst_temp
    END

 if type='A'
   select * into #comman from #temp
 else
   select * into #comman from #temp1

---- some operation on #comman table

After Executing Stored prcedure I got error

SQL

'There is already an object named '#comman ' in the database'

Even though I have Drop temporary table

Please tell me where i am wrong !!!

Posted 18-Dec-12 2:46am

madhuri@mumbai

Updated 18-Dec-12 18:29pm

v2

Add a Solution

Comments

[no name] 18-Dec-12 9:27am

There must be some problem in your drop table statement. Please check again.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

madhuri@mumbai · Answer 1 · 2012-12-18T22:09:00

Hello All,

I have resolved this problem,

SQL

IF OBJECT_ID('tempdb..#temp' ) IS NOT NULL DROP TABLE #temp;
 IF OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1;
 
CREATE table #comman
( abc varchar(10),
d varchar(10),
e varchar(10),
f varchar(10) );
  
If type ='A'
     BEGIN
        select abc,d,e,f into #temp from mst_temp
     END
  ELSE
     BEGIN
       select abc,d,temp1,'' as f into #temp1 from mst_temp
     END

  if type='A'
    Insert into #comman(abc,d,e,f) from #temp
  else
    Insert into #comman(abc,d,e,f) from #temp1

select * from #comman;
drop table #comman

I have just used insert instead of select and create table structure
becoz due to select its create new table.

Thanks all of you

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Answer 2 · 2012-12-18T03:29:00

I can see one problem in the query.

SQL

IF OBJECT_ID('tempdb..#temp1 ') IS NOT NULL DROP TABLE #temp1;
IF OBJECT_ID('tempdb..#comman ') IS NOT NULL DROP TABLE #comman ;

Here the Temporary tables names are not correct.
There are extra spaces after the names.

Instead of
1. OBJECT_ID('tempdb..#temp1'), it is written like
OBJECT_ID('tempdb..#temp1 ')

2. OBJECT_ID('tempdb..#comman'), it is written like
OBJECT_ID('tempdb..#comman ')

So do like below and try...

SQL

IF OBJECT_ID('tempdb..#temp' ) IS NOT NULL DROP TABLE #temp;
IF OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1;
IF OBJECT_ID('tempdb..#comman') IS NOT NULL DROP TABLE #comman;

ss29101976 · Answer 3 · 2012-12-18T04:04:00

Hello,

This is a parser/compile issue, it is not able to detect that there is a Drop statement.

Check below link, if it works well and good, otherwise below are 2 alternate solutions. [ may look ugly but should work]
http://stackoverflow.com/questions/4245444/there-is-already-an-object-named-columntable-in-the-database[^]

Solution 1:

1. Drop temp table, if it exists
2. Create a temp table with required columns.
3. insert values into the temp table using select statement.

Solution 2:

SQL

IF OBJECT_ID('tempdb..#comman ') IS NOT NULL DROP TABLE #comman ;
 IF OBJECT_ID('tempdb..#comman1 ') IS NOT NULL DROP TABLE #comman1 ;
 IF OBJECT_ID('tempdb..#comman2 ') IS NOT NULL DROP TABLE #comman2 ;
if type='A'
    select * into #comman from #temp
  else
    select * into #comman1 from #temp1
 IF OBJECT_ID('tempdb..#comman1 ') IS NOT NULL
    select * into #comman2 from #comman
else
     select * into #comman2 from #comman1

There is already an object named '#Temp' in the database,,related to Temp table

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us