SELECT INTO with a Temp Table

  • Richard Warr

    SSCertifiable

    Points: 6955

    Comments posted to this topic are about the item SELECT INTO with a Temp Table

    _____________________________________________________________________
    MCSA SQL Server 2012

  • deepak.a

    SSCertifiable

    Points: 5330

    hi,

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

    Thanks

    Deepak.A

  • Iulian -207023

    SSCertifiable

    Points: 7507

    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

    SSCertifiable

    Points: 5680

    Nice Question.Generally we will prefer to create the table first and then try to insert into it in these case.

  • Iulian -207023

    SSCertifiable

    Points: 7507

    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

    SSC Guru

    Points: 64645

    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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • GPO

    SSCarpal Tunnel

    Points: 4450

    @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 ones work is terribly important.... Bertrand Russell

  • Richard Warr

    SSCertifiable

    Points: 6955

    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 😉

    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

    SSCertifiable

    Points: 7507

    Thanks a lot Hugo

    Iulian

  • mister.magoo

    SSC-Forever

    Points: 47068

    Thanks for a good question.

    MM



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

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

  • LondonNick

    Say Hey Kid

    Points: 705

    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@gmail.com

    SSCarpal Tunnel

    Points: 4480

    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

    SSCertifiable

    Points: 7686

    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

    SSChampion

    Points: 11139

    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

    SSC Guru

    Points: 286408

    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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Viewing 15 posts - 1 through 15 (of 29 total)

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