T-SQL SELECT..INTO

Question

T-SQL SELECT..INTO

Viewing 15 posts - 16 through 30 (of 43 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

free_mascot (3/11/2014)
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)
...
Usually we do not consider exceptions hence for IDENTITY property too we have not consider conditions.

Who is this "we"?

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

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 2

Stewart "Arturius" Campbell (3/11/2014)
ronmoses (3/11/2014)
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
This refers to the ordinal positions of the columns in the table, not the order of the data being "inserted"

Yep, I misread it! Wow, too many words for this early in the morning, I guess. 😉

thanks

ron

-----
a haiku...

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

Yiremyahu SSCrazy Points: 2506 More actions · Answer 3

free_mascot (3/11/2014)
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.

I'm not sure I like this reasoning: the point of the question is presumably to test knowledge of SQL Server rather than the ability to work out what the question is asking.

Toreador SSChampion Points: 11368 More actions · Answer 4

free_mascot (3/11/2014)
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.

I don't not doubt that you don't really disbelieve that this isn't false...

sestell1 SSChampion Points: 10230 More actions · Answer 5

Based on the answers, I'm guessing it was intended that the SELECT statement was a simple one containing only one table. If the query contained more than one table, than the identity property would not carry over and the order of the rows would not be guaranteed.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

j.green (3/11/2014)
free_mascot (3/11/2014)
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.
I'm not sure I like this reasoning: the point of the question is presumably to test knowledge of SQL Server rather than the ability to work out what the question is asking.

Agreed. There is no reason to intentionally word things to be misleading.

_______________________________________________________________

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/

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 7

PChiragS

SSCarpal Tunnel

Points: 4965

March 11, 2014 at 7:17 am

#1695956

nice question..

Thomas Abraham SSChampion Points: 10761 More actions · Answer 8

j.green (3/11/2014)
free_mascot (3/11/2014)
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.
I'm not sure I like this reasoning: the point of the question is presumably to test knowledge of SQL Server rather than the ability to work out what the question is asking.

Saved me crafting my own response. +1 to this. But, liked the subject of the question. Thanks.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

webrunner SSC-Dedicated Points: 31882 More actions · Answer 9

Koen Verbeeck (3/11/2014)
Ed Wagner (3/11/2014)
The identity got me.
Oooh. Sounds like an exciting thriller.

Ha ha ha. Thanks, that made me laugh.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Koen Verbeeck (3/11/2014)
free_mascot (3/11/2014)
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)
...
Usually we do not consider exceptions hence for IDENTITY property too we have not consider conditions.
Who is this "we"?

I'd like to know as well. Exceptions and conditions are usually considered and explained in 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

Hany Helmy SSChampion Points: 13921 More actions · Answer 11

j.green (3/11/2014)
free_mascot (3/11/2014)
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.
I'm not sure I like this reasoning: the point of the question is presumably to test knowledge of SQL Server rather than the ability to work out what the question is asking.

+ 1

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 12

Ed Wagner (3/11/2014)
The identity got me.

😀

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

timwell SSCertifiable Points: 5315 More actions · Answer 13

ronmoses (3/11/2014)
Stewart "Arturius" Campbell (3/11/2014)
ronmoses (3/11/2014)
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
This refers to the ordinal positions of the columns in the table, not the order of the data being "inserted"
Yep, I misread it! Wow, too many words for this early in the morning, I guess. 😉

Hey, you database "experts" should know the difference between rows and columns....:-D

webrunner SSC-Dedicated Points: 31882 More actions · Answer 14

timwell (3/11/2014)
ronmoses (3/11/2014)
Stewart "Arturius" Campbell (3/11/2014)
ronmoses (3/11/2014)
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
This refers to the ordinal positions of the columns in the table, not the order of the data being "inserted"
Yep, I misread it! Wow, too many words for this early in the morning, I guess. 😉
Hey, you database "experts" should know the difference between rows and columns....:-D

Rows are just columns that fell asleep... right? 🙂

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Anoo S Pillai Mr or Mrs. 500 Points: 547 More actions · Answer 15

Rows are just columns that fell asleep... right? 🙂
- webrunner

especially when you are looking at them in early morning :hehe: