T-SQL SELECT..INTO

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

  • Interesting question, thanks.

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • Nice Question...

  • 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

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

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

  • 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

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

  • 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

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

  • 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

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

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