Error Using Temporary Tables With Same Names SQL Server

Question

Here is my scenario. (Following is my stored proc taking @date as an input parameter)

DECLARE @date DATE

If object_id('tempdb..#TempList') is not null drop table #TempList
go

Create table #TempList (MILL_NO VARCHAR(7), SHIP_DATE DATE, BL_STATUS NCHAR(1), 
FOOTAGE DECIMAL(12,4))

If @date IS NULL

Insert into #TempList
Select mill_no, null, bl_status,footage from fn_A(0,0)

Select * from #TempList


If object_id('tempdb..#TempList') is not null drop table #TempList
go

Create Table #TempList (MILL_NO VARCHAR(7), SHIP_DATE DATE, BL_STATUS NCHAR(1),
FOOTAGE DECIMAL(12,4))

If @date IS NOT NULL

Insert into #TempList
Select * from fn_B(0,'06/06/2006')

Select * from #TempList

I figured out from one of the posts that I cannot use temporary tables with same names unless I inclide a GO. However, including GO is not taking the parameters I try to pass.

Is there an alternate approach to eliminate this error?

Why drop and recreate the temporary table? Why not just truncate it? — LittleBobbyTables - Au Revoir
– LittleBobbyTables - Au Revoir, Commented Jul 19, 2012 at 14:41

LittleBobbyTables - Au Revoir · Accepted Answer · 2012-07-19 14:58:51Z

2

Based on the procedure supplied, you could use TRUNCATE TABLE instead.

I don't see a point dropping and recreating a table if all you want to do is quickly remove the records.

EDIT

You don't drop and recreate your table with the same name; instead of this code:

If object_id('tempdb..#TempList') is not null drop table #TempList go  
Create Table #TempList (
    MILL_NO VARCHAR(7), 
    SHIP_DATE DATE, 
    BL_STATUS NCHAR(1), 
    FOOTAGE DECIMAL(12,4)
)

Just do this:

TRUNCATE TABLE #TempList

edited Jul 19, 2012 at 14:58

answered Jul 19, 2012 at 14:43

LittleBobbyTables - Au Revoir

33k25 gold badges113 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rock Over a year ago

How can I have a temporary table with same name if not drop it ?

rock Over a year ago

Though I appreciate the fact that truncate deletes data in my #table, I don't understand the concept of it to resolve my issue here. My situation here is to insert data into the temporary tables(having same name) from two different functions (fn_A, fn_B). And I am unable to do that because I need to check if @date is null or not before inserting.

LittleBobbyTables - Au Revoir Over a year ago

Why do the temporary tables have to have the same name?

rock Over a year ago

Because the temp table needs to be called further down in the stored proc. Some additional functionality is to be applied on it. And creating two temporary tables will unnecessarily make my functionality redundant and complex.

Saic Siquot · Accepted Answer · 2012-07-19 16:00:46Z

0

if object_id('tempdb..#TempList') is allways NULL because #TempList is not the name that results created on tempdb's sysobjects table when you do create table #TempList

EDIT
what about this:

CREATE PROC PIRULO(@date as DATE) as

Create table #TempList (MILL_NO VARCHAR(7), SHIP_DATE DATE, BL_STATUS NCHAR(1), FOOTAGE DECIMAL(12,4))
IF @date IS NULL
    Insert into #TempList
    Select mill_no, null, bl_status,footage from fn_A(0,0)
ELSE
    Insert into #TempList
    Select * from fn_B(0,'2006/06/06')    -- also I changed date order.

Select * from #TempList

edited Jul 19, 2012 at 16:00

answered Jul 19, 2012 at 14:49

Saic Siquot

6,5135 gold badges37 silver badges56 bronze badges

5 Comments

rock Over a year ago

then why does SQL say 'There is already an object named '#TempList' in the database.'?

Martin Smith Over a year ago

The second part of this answer is correct but the first part isn't. OBJECT_ID works as the OP is using it.

Saic Siquot Over a year ago

SQL say 'There is already an object named '#TempList' because the drop table #TempList was not executed, because object_id('tempdb..#TempList') was null, because tempdb..#TempList is not the way to reference that table

Martin Smith Over a year ago

@LuisSiquot - You are wrong. Test it. Create table #TempList (x INT); SELECT object_id('tempdb..#TempList')

Saic Siquot Over a year ago

I must belive you, i have no access to mssql now

rock · Accepted Answer · 2012-07-20 14:58:47Z

0

I struggled all these days to insert values into same temporary table when a given condition is met.

Given that I am on a project migrating FoxPro(which has cursors declared all over) to SQL Server this small logic needed to be implemented in multiple stored procs.

Finally, what I learned out of this is - Think Straight Before Trying Anything Different (suggestion to my co-beginners in DB migration).

DECLARE @date DATE

SET @date = '06/06/2006'    --@date = null

If object_id('tempdb..#TempList') is not null drop table #TempList

Create table #TempList (MILL_NO VARCHAR(7), SHIP_DATE DATE, BL_STATUS NCHAR(1), 
FOOTAGE DECIMAL(12,4))

If @date = null

-- Here I am inserting null in place of Ship_Date because fn_A returns only 3 columns in my case
Insert into #TempList
Select mill_no, null, bl_status,footage from fn_A(0,0)

--Select * from #TempList

else

Insert into #TempList
Select * from fn_B(0,@date)

Select * from #TempList

Thanks everyone for giving your inputs. Hope this helps somebody.

edited Jul 20, 2012 at 14:58

answered Jul 20, 2012 at 12:55

rock

7053 gold badges11 silver badges26 bronze badges

4 Comments

Saic Siquot Over a year ago

isn't your code exactly what I had suggested?? in fact the only diference is the check of the existence of #TempList table. but, be sure it is redundant as temp tables has store procedure scope

rock Over a year ago

i did think about your answer when i got my own(like syntactically what you suggested is same).. but you suggested truncating the data and my requirement was to reuse #table with same name..

Saic Siquot Over a year ago

that is the other anwser, I have't mention TRUNCATE. you can at least mark as a good anwser, as you can mark many anwsers as "good answer"

rock Over a year ago

my apologies.. i mistook you to littlebobbytables.. coming to your answer you suggested object_id('tempdb..#TempList') is always null.. if thats the case my code will not execute more than once.. Also, do not get me wrong, I DID NOT down mark your answer.. I never do that.. But Thank you for your input.. I am doing 'answer useful' now.. Your answer did help me learn something new..

Collectives™ on Stack Overflow

Error Using Temporary Tables With Same Names SQL Server

3 Answers 3

4 Comments

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related