Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hi,
 

I am facing problem related to Temporary table...
 
I have create Storead procedure.
below is my scenario in SP.
 
 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
'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 3:46am
Edited 18-Dec-12 19:29pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hello All,
 
I have resolved this problem,
 
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
  Permalink  
Comments
__TR__ at 27-Dec-12 11:10am
   
+5
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I can see one problem in the query.
 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...
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;
  Permalink  
v2
Comments
Madhuri Patil at 19-Dec-12 0:29am
   
I have verified in SP,but its correct,bymistake i have inserted here space....
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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:
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
  Permalink  
Comments
Madhuri Patil at 19-Dec-12 0:43am
   
I have applied ur soln,sorry but its not worked its again shows me error message that 'There is already an object named '#comman2' in the database.'

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

  Print Answers RSS
0 OriginalGriff 277
1 Maciej Los 210
2 BillWoodruff 205
3 Jochen Arndt 180
4 Sergey Alexandrovich Kryukov 165
0 OriginalGriff 5,130
1 DamithSL 4,157
2 Maciej Los 3,670
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,821


Advertise | Privacy | Mobile
Web01 | 2.8.141216.1 | Last Updated 19 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100