SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SELECT INTO with a Temp Table


SELECT INTO with a Temp Table

Author
Message
LondonNick
LondonNick
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 516
I didn't read beyond the CREATE PROCEDURE. It wasn't the first line of the batch so I assumed "procedure would not run" would be the answer.



ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1219 Visits: 996
Excellent question. I came up with five or six possible reasons why the SP might not work, and ruled each of them out in turn. Of course I never thought of that one! :-) Glad to have learned something this morning.

Ron Moses

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3698 Visits: 4408
Iulian -207023 (11/18/2010)
Do you have at hand some best practices for using DML statements? I am thinking on something like:

...
SET @strQueryDML = 'SELECT ID INTO #tmpID2 FROM tbl1'
...
EXECUTE (@strQueryDML)
...
SELECT ID FROM #tmpID2



This code fails with the error message "Invalid object name #tmpID2". In this case, the local temprorary table "#tmpID2" exists only within the scope of the dynamic SQL. When the execution of the dynamic SQL is complete, SQL Server deletes the table. Of course, you may move the SELECT statement into the D-SQL piece of code, but it would be (to put it mildly) not the best decision :-)
sjimmo
sjimmo
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3744 Visits: 2903
Excellent question on a topic which is such a problem. I see this a lot from developers who wonder why it won't work. At first glance, it looks like it shuld and programatically should. But then apply logic;-)

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25954 Visits: 17519
Great question. Keep them coming!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jeff.mason
jeff.mason
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1457 Visits: 2137
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
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4796 Visits: 72518
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
For tips on how to post your problems
JohnFx
JohnFx
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 72
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
SanDroid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 1046
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
Rich Mechaber
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1549 Visits: 3665
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search