Insert Into problems

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

  • 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

  • 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

  • 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

  • Thanks, but easy! 😛

  • So QOTD is a double bluff today...

  • 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:.

  • 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.

  • Simple One........

  • 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

  • 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

  • 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!
  • 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.

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

  • 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 34 total)

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