T-SQL SELECT..INTO

Question

T-SQL SELECT..INTO

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

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

Yiremyahu SSCrazy Points: 2543 More actions · Answer 1

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: 11375 More actions · Answer 2

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 3

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: 286602 More actions · Answer 4

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 5

PChiragS

SSCarpal Tunnel

Points: 4965

March 11, 2014 at 7:17 am

#1695956

nice question..

Thomas Abraham SSChampion Points: 10761 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.

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: 31984 More actions · Answer 7

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 8

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 9

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 10

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 11

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: 31984 More actions · Answer 12

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 13

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

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

J DBA SSCertifiable Points: 5008 More actions · Answer 14

It was the DST change that got me. I was so tired that the double negatives confused me and I answered in the double negative and selected all the answers that were true.

Shouldn't I get half credit for working out the correct wrong answers, right?

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 15

Please , is it possible that someone explains me this sentence found in http://msdn.microsoft.com/en-us/library/ms188029.aspx ?

"When a sparse column is included in the select list, the sparse column property does not transfer to the column in the new table. If this property is required in the new table, alter the column definition after executing the SELECT...INTO statement to include this property"

According to me , "The sparse column property does transfer to the column in the new table" is exactly the contrary of what I have read in the BOL.

But it is possible that with my poor understanding of the English language , it is possible I have missed something.

Another remark : I have been warned that in the Microsoft exams , it is possible that I can find questions which are negative , that's to say , I would have to find the false proposals among several ones , a good way to disturb anybody especially when you are not using your mother tongue. This behavior is stupid if it is right that Microsoft is accepting such questions. Exams are to check whether you have good knowledge , if the person is asking such questions as today , I think that it is only to forbid to person to concentrate on the only useful thing : to show that he/she knows SQL Server. These tricky questions are not clever , only dishonest , but free mascot ( who created this question ) is not dishonest , he/she follows only the Microsoft behavior and it is an excellent way to prepare to the exam.

Only one problem ( which is a recurrent one ) : when you validate your choices , it is difficult to remember what they were and to know what we have replied in the false way.

T-SQL SELECT..INTO

Cookies on SQLServerCentral