SELECT INTO

Question

SELECT INTO

Ron McCullough

SSC Guru

Points: 63877
More actions
June 12, 2010 at 11:56 pm

#233645

Comments posted to this topic are about the item SELECT INTO
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

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

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

UMG Developer SSChampion Points: 13482 More actions · Answer 1

I got it wrong because I didn't read the question correctly, I thought it was asking how many rows where returned.

But in any case even 3 is not the correct answer, because as the graphic is displayed you would get an error on the CREATE TABLE statement.

antony-688446 Ten Centuries Points: 1221 More actions · Answer 2

Come on, give the guy a break - you can see that Null has been cropped off! Not another huge discussion on assumptions!

I am on the other side this time, as I read the question right, made the correct assumptions, & got it right 🙂

UMG Developer SSChampion Points: 13482 More actions · Answer 3

antony-688446 (6/13/2010)
Come on, give the guy a break - you can see that Null has been cropped off! Not another huge discussion on assumptions!

But for all you know there could have been more cropped off. say "ll, Field3 int NULL)" or anything else that could have caused an error, so you only assumed that what was cropped was "ll)".

What it comes down to is either we have to be able make assumptions/corrections to the code, or the correct answer should be based on exactly what is shown. (Which means the person/process posting a new question has to validate that it came out correctly.)

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 4

i didn't read question correctly, and got it wrong... :(. anyway good question, but presented badly...

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 5

good question on select into...

I had one point to add.

the following gives an error.

select IDENTITY(int,100,5),* into #temp3 from

testread

there is no alias for identity column.so it shold throw an error.because every column has name in database. without column name it throws error.

one point we should remember is all columns must have a name when we copy data using select into.here identity column should have alias, then it will execute sucess.with out alias for functions when we copy data using select into it throws an error.For CTE also it sholud apply.

for all functions and numerics it also applies.

Please forgive me for my english.I will improve.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

ziangij SSCertifiable Points: 7991 More actions · Answer 6

i guess bitbucket has already provided the alias in his question!

malleswarareddy_m (6/14/2010)
good question on select into...
I had one point to add.
the following gives an error.
select IDENTITY(int,100,5),* into #temp3 from
testread
there no alias for identity column.so it shold throw an error.because every column has name in database. without column name it throws error.
one point we should remember is all columns must have a name when we copy data using select into.here identity column should have alias, then it will execute sucess.with out alias for functions when we copy data using select into it throws an error.For CTE also it sholud apply.
for all functions and numerics it also applies.
Please forgive me for my english.I will improve.

ziangij SSCertifiable Points: 7991 More actions · Answer 7

yep, i too assumed it to be NULL and i got it correct... 😀

antony-688446 (6/13/2010)
Come on, give the guy a break - you can see that Null has been cropped off! Not another huge discussion on assumptions!
I am on the other side this time, as I read the question right, made the correct assumptions, & got it right 🙂

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 8

I cant see any scalar value getting inserted or am i missing something

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 9

Nice Question on the Select Into

for a second i thought Question as How many Rows then while selecting the answer i saw it as columns :hehe:

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 10

Nice question, although not that hard for me as I use SELECT INTO a lot.

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 11

sharath.chalamgari (6/14/2010)
Nice Question on the Select Into
for a second i thought Question as How many Rows then while selecting the answer i saw it as columns

Oh. And i just saw it after you pointed it out. I read the question very carefully and still missed it. Good question though.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

DugyC Hall of Fame Points: 3804 More actions · Answer 12

Read the question correctly, assumed a cropped NULL... come on! people, stop being pedantic!!

Still got it wrong, knew the correct answer but, through a lack of confidence, suspected a trick in the question and went with 2 instead.

Doh!! :doze:

_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

paul.knibbs SSCoach Points: 15320 More actions · Answer 13

I said it would return zero thanks to the cropped NULL, but even without that, surely it would return 4 rows, not 3? I would expect the values from Table 1 to be duplicated twice in Table 2 with an attached Identity column...or am I missing something?

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 14

I don't really get the discussion on the chopped off graphic. If you assume an extra column there, the answer would be 4, which was not listed - making it obvious that no column was chopped off.

I am surprised that this relatively simple question is worth 2 points. It made me wonder for a time if I was missing something. I was almost tempted to choose the "error" because the "Int" (instead of "int") would cause an error when run in a database with case-sensitive collation. But since case insensitive is the more common choice, Ron is the question author, and nothing was mentioned about collation, I decided that it was safe to assume the more standard case insensitive collation.

I'm not sure what the intended lesson of the question is, though. Maybe the use of the IDENTITY() function in SELECT INTO?

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/