drop temp table in dynamic SQL

Question

drop temp table in dynamic SQL

deeby

Old Hand

Points: 342
More actions
February 20, 2011 at 3:53 am

#245887

I'm importing excel data into SQL using SQLOLEDB, building up and then executing a command string with multiple OPENROWSET instructions.
I want to use a temporary table within the command string, so that I can read data into the temp table, and then build different code depending on how many columns are in the temp table. So the code repeats instructions like
INSERT INTO #TEMP; [do OPENROWSET processing 1]; DROP TABLE #TEMP;
INSERT INTO #TEMP; [do OPENROWSET processing 2]; DROP TABLE #TEMP;
etc.
But it seems that when the code executes, the temp table isn't dropped before the next INSERT INTO is executed - in short it doesn't work.
Can anyone tell me how to fix it?

Viewing 12 posts - 1 through 11 (of 11 total)

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

John.Liu SSCommitted Points: 1606 More actions · Answer 1

The instruction looks OK. I assumed you received table #TEMP already exist error message. Any chance you can post your actual code?

deeby Old Hand Points: 342 More actions · Answer 2

I can't get at the original code at present but yes, the problem is exactly that: the second set of instructions receives message "cannot create table #TEMP" because it already exists, despite DROP TABLE command in first set of instructions.

Is there some kind of GO / RUN / COMMIT command I need to include?

I get the problem when I build up a command string variable @CMDstring, then EXEC (@CMDstring)

If I print @CMDstring, I can take the message output (which is all Tsql instructions) and run that in SQL studio, and it works fine.

So there is some difference in the way the temp tables are being treated when run as an EXEC.

John.Liu SSCommitted Points: 1606 More actions · Answer 3

Try drop it just before the insert and see if that helps.

IF OBJECT_ID('TEMPDB..#TEMP') > 0 DROP TABLE #TEMP

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

I usually use syntax below...

if exists (select * from sys.tables where name like '#mytemptable%')

drop table #mytemptable

create table #mytemptable(table definition as needed)

Please replace "mytemptable" with the appropriate name.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

John.Liu SSCommitted Points: 1606 More actions · Answer 5

PaulB-TheOneAndOnly (2/20/2011)
I usually use syntax below...
if exists (select * from sys.tables where name like '#mytemptable%')

temp table is created in TEMPDB, unless you already in the TEMPDB, you have to fully qualify the table, correct syntax should be:

if exists (select * from tempdb.sys.tables where name like '#mytemptable%')

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

John.Liu (2/21/2011)
PaulB-TheOneAndOnly (2/20/2011)
I usually use syntax below...
if exists (select * from sys.tables where name like '#mytemptable%')
temp table is created in TEMPDB, unless you already in the TEMPDB, you have to fully qualify the table, correct syntax should be:
if exists (select * from tempdb.sys.tables where name like '#mytemptable%')

Yes - that's correct.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

Deeby, can you post your actual code? It may have to do with your methodology.

If I'm right, you're getting something like the results of this:

SELECT 'abc' AS txt INTO #tmp

DROP TABLE #tmp

SELECT 'def' AS txt INTO #tmp

DROP TABLE #tmp

SELECT 'efg' AS txt INTO #tmp

SELECT * FROM #tmp

The compiler won't let you get away with it, even though your logic is correct. The compiler just doesn't know better. You're going to have to use CREATE, INSERT INTO, and DROPs to avoid this, instead of SELECT INTOs.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 8

Craig Farrell (2/21/2011)
Deeby, can you post your actual code? It may have to do with your methodology.
If I'm right, you're getting something like the results of this:
SELECT 'abc' AS txt INTO #tmp
DROP TABLE #tmp
SELECT 'def' AS txt INTO #tmp
DROP TABLE #tmp
SELECT 'efg' AS txt INTO #tmp
SELECT * FROM #tmp
The compiler won't let you get away with it, even though your logic is correct. The compiler just doesn't know better. You're going to have to use CREATE, INSERT INTO, and DROPs to avoid this, instead of SELECT INTOs.

Umm... nope... I believe that's a problem, as well.

CREATE TABLE #Tmp (txt VARCHAR(5))

INSERT INTO #Tmp (txt) SELECT 'abc'

DROP TABLE #Tmp

CREATE TABLE #Tmp (txt VARCHAR(5))

INSERT INTO #Tmp (txt) SELECT 'def'

DROP TABLE #Tmp

CREATE TABLE #Tmp (txt VARCHAR(5))

INSERT INTO #Tmp (txt) SELECT 'ghi'

DROP TABLE #Tmp

The only way to do this using the same Temp Table name is with dynamic SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

deeby (2/20/2011)
I'm importing excel data into SQL using SQLOLEDB, building up and then executing a command string with multiple OPENROWSET instructions.
I want to use a temporary table within the command string, so that I can read data into the temp table, and then build different code depending on how many columns are in the temp table. So the code repeats instructions like
INSERT INTO #TEMP; [do OPENROWSET processing 1]; DROP TABLE #TEMP;
INSERT INTO #TEMP; [do OPENROWSET processing 2]; DROP TABLE #TEMP;
etc.
But it seems that when the code executes, the temp table isn't dropped before the next INSERT INTO is executed - in short it doesn't work.
Can anyone tell me how to fix it?

There's only one way that I know of and that's with Dynamic SQL. But first, I have to ask, with Temp Table names being a dime a dozen, why so much concern over using the same Temp Table name?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 10

Jeff Moden (2/21/2011)
Umm... nope... I believe that's a problem, as well.
<snip proof>
The only way to do this using the same Temp Table name is with dynamic SQL.

Dangit, you're right. Should have tested that all the way through. I was thinking of another scenario.

Thanks for catching and fixing that before the OP ended up trying to figure out why my way wasn't working either. :hehe:

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 11

Craig Farrell (2/21/2011)
Jeff Moden (2/21/2011)
Umm... nope... I believe that's a problem, as well.
<snip proof>
The only way to do this using the same Temp Table name is with dynamic SQL.
Dangit, you're right. Should have tested that all the way through. I was thinking of another scenario.
Thanks for catching and fixing that before the OP ended up trying to figure out why my way wasn't working either. :hehe:

The only way that I knew it was because I've been burned by it before. :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)