Insert Into problems

  • Henrik Staun Poulsen

    SSCertifiable

    Points: 6408

    Comments posted to this topic are about the item Insert Into problems

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    Thank you for the question but I guess that's straightforward.

    The "AS colX" parts of the SELECT statement are aliases to the columns returned by the SELECT statement and they don't reference column positions. The INSERT statement will follow the order specified inside the parenthesis and if no columns were specified in that statement, it will follow the creation order of the columns.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • SQLRNNR

    SSC Guru

    Points: 281243

    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

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    As said before, the AS clause in the SELECT statement is for aliasing the columns, which is disregarded by the INSERT statement.

    Your statement should have looked like this:

    INSERT INTO #ATable (col2, col1, col3)

    ...

    if you wanted to change the order of inserts.

    Appreciate the effort of putting this in a QoTD.

    (and I was looking frantically for 5 minutes to find the "catch". :-D)

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

  • palotaiarpad

    SSCertifiable

    Points: 5506

    Thanks, but easy! 😛

  • paul s-306273

    SSChampion

    Points: 10615

    So QOTD is a double bluff today...

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Koen Verbeeck (11/28/2011)


    (and I was looking frantically for 5 minutes to find the "catch". :-D)

    Glad to see I had so much effects on you all :hehe:.

  • Henrik Staun Poulsen

    SSCertifiable

    Points: 6408

    Well, since I wrote it up as a QotD, you may not need three guesses as to who went into this "trap" big time.

    I've spent a few hours, before I noticed the bug.

  • sharath.chalamgari

    SSCertifiable

    Points: 5680

    Simple One........

  • InvisibleCat

    Default port

    Points: 1496

    I've spent a few hours, before I noticed the bug.

    It may just be me, but I wouldn't class this a bug, if that's what are you are saying, the AS is (as has been stated in this post) for the alias, it has no bearing on the order of the insert.

    None the less a fun question, so thank you.

    Nic

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Nic-306421 (11/29/2011)


    I've spent a few hours, before I noticed the bug.

    It may just be me, but I wouldn't class this a bug...

    It's not just you 😉

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

  • rfr.ferrari

    SSCertifiable

    Points: 6879

    thanks for question!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • Henrik Staun Poulsen

    SSCertifiable

    Points: 6408

    well, it was a bug...

    In my own code!

    I know; I created it myself.

    Easy for me to fix, as soon as I noticed that column order was important.

    It is a bit like this problem in SSIS:

    Will this round or truncate?

    (DT_I4)(SpeedIntervalFrom / 0.5)

    It is an expression in a Derived column component.

    Well, it should have been

    (DT_I4)FLOOR(SpeedIntervalFrom / 0.5)

    because we wanted it to truncate.

  • rajucse.kumar

    Newbie

    Points: 9

    It is simply can be vieved, value inserted in respective columns will be in column order ...

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Nice question. Not terribly hard, but interesting. It could have been made a bit harder by omitting the column list in the INSERT:

    INSERT INTO #ATable

    SELECT 2 AS col2, 1 AS col1, 3 AS col3

    For the record, I am not condoning the above as good practice. In my book, both omitting the column list of the INSERT statement and adding aliases to the SELECT list of an INSERT ... SELECT are bad practices.

    A final remark - I don't see how the version in the explanation, that uses comments instead of aliases, is any clearer than the original. The column names in the comments don't match reality, so these comments are obfuscating the code instead of clarifying it.

    Here's the way I write INSERT statements, to make it easier to say which expression in the SELECT list matches which column in the INSERT list:

    INSERT INTO #ATable

    (col1, col2, col3)

    SELECT 2, 1, 3;

    (Where I change the column positions based on the length of the column names and expressions in the SELECT list).

    I usually limit myself to three columns per line, because I don't like to scroll horizontally. So when there's a long column list, I use this style:

    INSERT INTO #BTable

    (col1, col2, col3,

    col4, col5, col6,

    col7, col8)

    SELECT 2, 1, 3,

    4, 5, 6,

    7, 8;


    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/

Viewing 15 posts - 1 through 15 (of 35 total)

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