T-SQL SELECT..INTO

  • free_mascot

    One Orange Chip

    Points: 27168

    Comments posted to this topic are about the item T-SQL SELECT..INTO

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Ford Fairlane

    SSCertifiable

    Points: 7664

    Interesting question, thanks.

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • PRAMANA.DBA

    SSCertifiable

    Points: 5507

    Nice Question...

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Great question.

    Although the IDENTITY property doesn't always transfer:

    When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

    *The SELECT statement contains a join, GROUP BY clause, or aggregate function.

    *Multiple SELECT statements are joined by using UNION.

    *The identity column is listed more than one time in the select list.

    *The identity column is part of an expression.

    *The identity column is from a remote data source.

    INTO Clause (Transact-SQL)

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

  • This was removed by the editor as SPAM

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Good one. Thank you for the post.

    :blush: out of three, got two right...

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Toreador

    SSChampion

    Points: 11259

    Took a lot of thinking to work out all those double negatives!

  • Neil Burton

    SSC-Insane

    Points: 22249

    Toreador (3/11/2014)


    Took a lot of thinking to work out all those double negatives!

    +1


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Richard Warr

    SSCertifiable

    Points: 6957

    Toreador (3/11/2014)


    Took a lot of thinking to work out all those double negatives!

    Yes, I fell down on the Filestream one because of that!

    _____________________________________________________________________
    MCSA SQL Server 2012

  • Ed Wagner

    SSC Guru

    Points: 286985

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Ed Wagner (3/11/2014)


    The identity got me.

    Oooh. Sounds like an exciting thriller.

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

  • free_mascot

    One Orange Chip

    Points: 27168

    Yes, initially I have drafted the question straight forward but it looks very simple. Deliberately make it complicated by tricky language to make challenging.

    Usually everyone knows correct answer but when things presented differently; makes it difficult.

    Appreciate all your efforts and comments.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    The answers appear to suggest that the following is true:

    "The columns in new_table are created in the order specified by the select list."

    However, http://technet.microsoft.com/en-us/library/ms188029.aspx says...

    "Specifying an ORDER BY clause does not guarantee the rows are inserted in the specified order."

    Am I misreading something?

    ron

    -----
    a haiku...

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

  • free_mascot

    One Orange Chip

    Points: 27168

    Koen Verbeeck (3/11/2014)


    Great question.

    Although the IDENTITY property doesn't always transfer:

    When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

    *The SELECT statement contains a join, GROUP BY clause, or aggregate function.

    *Multiple SELECT statements are joined by using UNION.

    *The identity column is listed more than one time in the select list.

    *The identity column is part of an expression.

    *The identity column is from a remote data source.

    INTO Clause (Transact-SQL)

    Hello Koen,

    This comment was expected for this question. 🙂

    Usually we do not consider exceptions hence for IDENTITY property too we have not consider conditions.

    Thank you.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • This was removed by the editor as SPAM

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

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