SELECT INTO with a Temp Table

Question

SELECT INTO with a Temp Table

Viewing 14 posts - 16 through 28 (of 28 total)

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

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 1

Iulian -207023 (11/18/2010)
One innocent remark that does not minimize the high value of the question. I am thinking to drop tbl1 and tb2 tables at the end of the script:
DROP TABLE tbl1
DROP TABLE tbl2
and to place a GO statement before CREATE PROCEDURE since 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Regards,
Iulian

This is exactly why I got this right -- I assumed that the procedure would not compile because it was not the first statement in a batch the way it was written.

mtassin SSC-Insane Points: 23099 More actions · Answer 2

Iulian -207023 (11/18/2010)
Great point. Amazing, the compiler see two creations of the #tmpID table and stops the procedure from compiling.
Do you have at hand some best practices for using DML statements? I am thinking on something like:
CREATE PROCEDURE QOTD (@source INT)
AS
BEGIN
DECLARE @strQueryDML AS VARCHAR(100)
IF @source = 1
SET @strQueryDML = 'SELECT ID INTO #tmpID2 FROM tbl1'
ELSE
SET @strQueryDML = 'SELECT ID INTO #tmpID2 FROM tbl2'
EXECUTE (@strQueryDML)
SELECT ID FROM #tmpID2
END

The DML won't work that way because the temp table that you create in the DML will be released when the DML finishes executing.

One of two solutions to this.

IF @source = 1

SET @strQueryDML = 'SELECT ID INTO #tmpID2 FROM tbl1;

SELECT * FROM #tmpID2'

ELSE

SET @strQueryDML = 'SELECT ID INTO #tmpID2 FROM tbl2;

SELECT * FROM #tmpID2'

Or just create the temp table before the IF/ELSE block and use insert INTO instead.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

JohnFx SSC Enthusiast Points: 155 More actions · Answer 3

Got it right, but apparently not for the knowledge that the question was intended to test for.

I assumed it wouldn't compile because the create procedure part wasn't in its own batch.

SanDroid SSChampion Points: 10068 More actions · Answer 4

I also was one that selected the right answer because thier where so many errors in the code I determined that was either the right answer, or the one most people would select.

Is there an FAQ for people making QOTD submisions?

You would think that as long as QOTD has been going on that we would have one.

I know I could use one.

Rich Mechaber SSChampion Points: 11130 More actions · Answer 5

Hugo Kornelis (11/18/2010)
Good question; many people would expect this to work. (As I did, the first time I ran into this. And the second, third, ... - I answered correctly but only because I've run into this often enough to cause a permanent dent in my forehead).

I can only echo Hugo's comment about why I immediately knew the answer before seeing the available multiple choice options: miserable first-hand experience.

In my case, though, "third" would be followed by "nth", where n was an embarrassingly large integer. :hehe:

The not-quite-truthful error-message returned in SSMS, "There is already an object named '#MyNonexistentTempTable' in the database.", had me trying to DROP a non-existent table, with, of course, an unproductive outcome.

My take-home lesson on this is that T-SQL doesn't really like even the few elements of procedural code provided to us (couldn't we please have ELSEIF???) The compiler pretty much ignores the fact that the 2 SELECT..INTO statements are mutually exclusive executions.

Good question!

Rich

UMG Developer SSChampion Points: 13482 More actions · Answer 6

Thanks for the question, I too have run into this and now know better.

As for the missing GOs I thought about that being a problem, but since you had each command separated into a separate code block I assumed that wasn't your intent. (Even though the question was "What would be the result returned from the following script?")

Koen Verbeeck SSC Guru Points: 259125 More actions · Answer 7

Nice question and great follow up discussion.

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

ziangij SSCrazy Eights Points: 8066 More actions · Answer 8

ziangij

SSCrazy Eights

Points: 8066

November 18, 2010 at 11:00 pm

#1252864

nice question... learned something new today...:-)

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 9

Thanks for the explanation vk-kirov and mtassin.

Regards,

Iulian

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 10

Good question, thanks for taking the time to help educate the SQL community.

Hope this helps,
Rich

[p]
[/p]

webrunner SSC-Dedicated Points: 31891 More actions · Answer 11

This is crazy -- I didn't even think of the problem that happens, since the two table creation statements are in different parts of the IF block. But if that's the way it works, then it is good to know.

Thanks,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Richard Warr SSCertifiable Points: 6967 More actions · Answer 12

RichardDouglas (11/22/2010)
Good question, thanks for taking the time to help educate the SQL community.

I assure you that it's nearly always the other way around 😉

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

zymos SSCommitted Points: 1960 More actions · Answer 14

Great question on the stored procedure.

Thanks. 🙂