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
Richard Warr
Richard Warr
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3596 Visits: 1992
Comments posted to this topic are about the item SELECT INTO with a Temp Table

_____________________________________________________________________
MCSA SQL Server 2012
deepak.a
deepak.a
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1800 Visits: 863
hi,

Nice Question . Can u pls provide the reference links to understand more about this.

Thanks
Deepak.A
Iulian -207023
Iulian -207023
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2885 Visits: 1248
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


sharath.chalamgari
sharath.chalamgari
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2286 Visits: 798
Nice Question.Generally we will prefer to create the table first and then try to insert into it in these case.
Iulian -207023
Iulian -207023
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2885 Visits: 1248
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
Hugo Kornelis
Hugo Kornelis
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21881 Visits: 12484
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).

deepak.a (11/17/2010)
Can u pls provide the reference links to understand more about this.

I'm not sure if this is documented anywhere (if it is, I've never found it), but the explanation is that SQL Server uses is a one-pass parser and comiler. That implies that it reads the code once, top to bottom and left to right. Variables have to be declared before they are referenced, and exactly once. Similar for table creation and referencing.
One effect of this one-pass parser is that control-flow is not evaluated. So a variable declaration in a loop is not an error, and creating a table in a loop will not cause a compile-time error (the error will be at run-time, during the second execution of the loop). It is also possible to have declarations in code paths that will never execute. For instance, this code compiles and executes just fine:
IF 1 = 2
BEGIN;
DECLARE @a int;
END;
SET @a = 1;
PRINT @a;


And similarly, this code will execute without errors, but fails at run-time.
IF 1 = 2
BEGIN;
CREATE TABLE #xyz (a int)
END;
INSERT INTO #xyz(a) VALUES(1);
SELECT * FROM #xyz;



This rarely ever affects "normal" code - except in cases such as this question, where people try to use SELECT INTO in two different branches of an IF statement.

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:

No, I would not recommend resorting to dynamic SQL. As the explanation of the question suggests, the best course of action is to create the table seperately, then fill it in the IF. So the code in this question should change to either
CREATE PROCEDURE QOTD (@source INT)
AS
BEGIN;
CREATE TABLE #tmpID (ID int);
IF @source = 1
INSERT INTO #tmpID (ID)
SELECT ID FROM tbl1;
ELSE
INSERT INTO #tmpID (ID)
SELECT ID FROM tbl2;

SELECT ID FROM #tmpID;
END;


or
CREATE PROCEDURE QOTD (@source INT)
AS
BEGIN;
SELECT ID INTO #tmpID FROM tbl1 WHERE 1 = 2;
IF @source = 1
INSERT INTO #tmpID (ID)
SELECT ID FROM tbl1;
ELSE
INSERT INTO #tmpID (ID)
SELECT ID FROM tbl2;

SELECT ID FROM #tmpID;
END;




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
GPO
GPO
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1934 Visits: 1941
@Iulian -207023: Yes totally agree about the GO and drops. Wasn't sure whether the missing GO was some trick part of the question. Very interesting quirk to be aware of.

:-)

One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.
Bertrand Russell

Richard Warr
Richard Warr
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3596 Visits: 1992
Thanls for the feedback so far. Apologies, the GO and Drops were an omission but I hope that didn't detract too much from the meaning behind the question. It's the first contribution I've made to the site so I'll do better next time Wink
Like Hugo I only came across this when I tried to do it at work so I thought I'd share the experience.

_____________________________________________________________________
MCSA SQL Server 2012
Iulian -207023
Iulian -207023
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2885 Visits: 1248
Thanks a lot Hugo

Iulian
mister.magoo
mister.magoo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14479 Visits: 7896
Thanks for a good question.

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • 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