#temp table already exists error

Question

#temp table already exists error

arukannan

SSC Eights!

Points: 889
More actions
March 10, 2015 at 12:53 am
Go to Answer
#298406

IF 1=2
BEGIN
IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMP
SELECT 1 AS ID, 'ABC' NAME INTO #TEMP
END
ELSE
BEGIN
IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMP
SELECT 2 AS ID, 'DEF' NAME INTO #TEMP
END
i get the below error if i run this,
Msg 2714, Level 16, State 1, Line 10
There is already an object named '#TEMP' in the database.
Gail Shaw

SSC Guru

Points: 1004504
More actions
March 10, 2015 at 2:51 am
Answer
#1782797

The SQL parser does not and can not execute code. It's a parser. It encounters the same temp table being created twice, hence throws an error.
Solution: Create the temp table once and only once.
CREATE TABLE #Temp (
ID int,
Name char(3)
);
IF 1=2
INSERT INTO INTO #TEMP
SELECT 1 AS ID, 'ABC' NAME ;
ELSE
INSERT INTO INTO #TEMP
SELECT 2 AS ID, 'DEF' NAME;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

I'm not 100% sure what happens, but I think the error is a bit misleading.

SQL Server will parse the SQL statement and validate it before it actually executes it. Maybe SQL Server does not realize the first path will never be executed and errors out on the fact that you might create the temp tables twice.

Anyway, it's just a guess 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

arukannan SSC Eights! Points: 889 More actions · Answer 2

Thanks for your reply.

I too believe the same, it assumes the table gets created in the first block itself even though it does not get executed.

Can someone help me how to fix this issue?

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

I'm afraid you need to create the temp table first, and then use the regular INSERT ... SELECT syntax.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

GilaMonster (3/10/2015)
The SQL parser does not and can not execute code. It's a parser. It encounters the same temp table being created twice, hence throws an error.
Solution: Create the temp table once and only once.
CREATE TABLE #Temp (
ID int,
Name char(3)
);
IF 1=2
INSERT INTO INTO #TEMP
SELECT 1 AS ID, 'ABC' NAME ;
ELSE
INSERT INTO INTO #TEMP
SELECT 2 AS ID, 'DEF' NAME;

Additionally, the same happens when you work with temp variables, you'll be given a parser error; i.e. you have to declare them only once.

Igor Micev,My blog: www.igormicev.com

arukannan SSC Eights! Points: 889 More actions · Answer 5

Hi All,

I agree with you that parset encounters the same statement twice and hence it errors.

But in my case, am creating table structure dynamically by "select * into #temp"

So, i should think about creating it initially. But if there is any workaround other than this, pls post it

Thanks all for your help!