Temp table not deleted when exec SQL string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Temp table not deleted when exec SQL string

Author

Topic

IK1972

56 Posts

Posted - 2011-11-29 : 11:45:32

When I try to run below script Getting error :
There is already an object named '#mytemp' in the database.

any Idea why and how to fix this.

declare @sqlQuery varchar(max)
SET @sqlQuery ='
IF object_id(''tempdb..#mytemp'') IS NOT NULL
DROP TABLE #mytemp;

SELECT T517.* INTO #mytemp
FROM T517 WITH (NOLOCK)

TRUNCATE TABLE T517;

INSERT INTO T517 WITH (TABLOCK) (UserID, AttributeValue, CreationTime)
SELECT UserID, AttributeValue, CreationTime FROM #mytemp;

IF object_id(''tempdb..#mytemp'') IS NOT NULL
DROP TABLE #mytemp;

SELECT T518.* INTO #mytemp
FROM T518 WITH (NOLOCK)

TRUNCATE TABLE T518;

INSERT INTO T518 WITH (TABLOCK) (UserID, AttributeValue, CreationTime)
SELECT UserID, AttributeValue, CreationTime FROM #mytemp;

IF object_id(''tempdb..#mytemp'') IS NOT NULL
DROP TABLE #mytemp;

SELECT T519.* INTO #mytemp
FROM T519 WITH (NOLOCK)

TRUNCATE TABLE T519;

INSERT INTO T519 WITH (TABLOCK) (UserID, AttributeValue, CreationTime)
SELECT UserID, AttributeValue, CreationTime FROM #mytemp;
'

exec (@sqlQuery);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-29 : 11:49:55

really i didnt understand why you're using dynamic sql here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-29 : 12:04:50

This is a "feature" of the SQL parser, which if you ask me, seems like a bug/deficiency (But, they didn't ask me, can you believe it!?!?!). For example, try this code, and it shows a parsing error.


IF object_id('tempdb..#mytemp') IS NOT NULL 
DROP TABLE #mytemp;	

select 1 as col1 into #mytemp;

IF object_id('tempdb..#mytemp') IS NOT NULL 
DROP TABLE #mytemp;	

select 1 as col1 into #mytemp;

And it says


Msg 2714, Level 16, State 1, Line 9
There is already an object named '#mytemp' in the database.

I have worked around it by, for example using different temp table names, or using batch separator between creations of the table etc.

IK1972

56 Posts

Posted - 2011-11-29 : 12:11:16

quote:
Originally posted by sunitabeck

This is a "feature" of the SQL parser, which if you ask me, seems like a bug/deficiency (But, they didn't ask me, can you believe it!?!?!). For example, try this code, and it shows a parsing error.

IF object_id('tempdb..#mytemp') IS NOT NULL
DROP TABLE #mytemp;

select 1 as col1 into #mytemp;

IF object_id('tempdb..#mytemp') IS NOT NULL
DROP TABLE #mytemp;

select 1 as col1 into #mytemp;
And it says

Msg 2714, Level 16, State 1, Line 9
There is already an object named '#mytemp' in the database.

I have worked around it by, for example using different temp table names, or using batch separator between creations of the table etc.

in query analyser if you use GO as batch terminator then its work but with dynamic SQL its not work and in my case i have to use dynamic SQL.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-29 : 12:38:01

I've never run into this becaue I always explicitly create my (temp) tables. So I was looking at the SQL documentation to see if this is mentioned and, to my surprise, it was (well in a way

quote:
Important

DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources