SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


INTO Identities


INTO Identities

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: Administrators
Points: 145229 Visits: 19425
Comments posted to this topic are about the item INTO Identities

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
zerko
zerko
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 712
I may be wrong here, but this bug could have been around even longer. SQL2008 doco http://msdn.microsoft.com/en-us/library/ms188029(v=sql.100).aspx also shows the same comment about the effect of GROUP BY clause on the 'Identity' property inheritence, but if I run the following code on our 2008 installation, the results of the final SELECT statement show a 1 in the 'is_identity' column of CustomerID for both objects.

create table #customer (CustomerID int identity, city int)

insert into #customer values (1),(2),(3),(4),(2),(3),(4),(3),(4),(4)

Select CustomerID, count(city) as City_Count
into #temp
from #customer group by CustomerID

select * from tempdb.sys.columns



I note that unless I provide a name for the aggregation, SQL2008 will complain that 'An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.'

From a 'usefulness' perspective, I also note that since CustomerID is an IDENTITY column, the results of the COUNT(city) aggregate should always be 1. Maybe the fact that aggregation against an Identity column has such limited usefulness has kept the bug hidden for all this time? :-D
Danny Ocean
Danny Ocean
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2216 Visits: 1549
Good Basic question. But i surprised that still 51% people are wrong. w00t

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
Slawomir Swietoniowski
Slawomir Swietoniowski
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3793 Visits: 595
This question is stated wrong, cont(...) does not have any alias for the name, so, while you will try to execute this code, you will get:

Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

My guess is that is why only ~50% percent people guessed correctly, the rest might assume that lack of an alias is the "tricky" part of this question...

Slawomir Swietoniowski, MCITP:DBA+Developer (2005/2008)
Danny Ocean
Danny Ocean
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2216 Visits: 1549
Slawomir Swietoniowski (5/12/2013)
This question is stated wrong, cont(...) does not have any alias for the name, so, while you will try to execute this code, you will get:

Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

My guess is that is why only ~50% percent people guessed correctly, the rest might assume that lack of an alias is the "tricky" part of this question...


I agree with you. I think this question missed one another option in answer, its "Error".
Question ask about copy identity property, For this answer is "YES", But Script contains error. Due to this, it's completely mess-up.
I think nobody check the question before published and everybody should get point for this.

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
sqlnaive
sqlnaive
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6367 Visits: 2774
I understand there was an error. But keeping an eye on the subject I ignored it (though it should have been rectified). I remembered it as my real time experience but that time it was into temporary table. So here I tried it for physical table and it gave same result. Didn't check BOL but looking at past comments seems to be a bug.

Nevertheless a good question and good start to week. :-)
RossRoss
RossRoss
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 763
Good question to start the week off.

Given the available answers, the lack of an alias doesn't really matter, anyone reading the question and options to choose from should realise they'll need an alias if writing it out so it doesn't really affect the question at all.
call.copse
call.copse
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5582 Visits: 2008
I agree this was obviously not a question about the error from lack of an alias. Interesting one which I did not know.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61881 Visits: 13297
Danny Ocean (5/12/2013)
Good Basic question. But i surprised that still 51% people are wrong. w00t


Yes, because I use INTO combined with a GROUP BY daily to create new tables... Rolleyes


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
vinu512
vinu512
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3625 Visits: 1626
Slawomir Swietoniowski (5/12/2013)
This question is stated wrong, cont(...) does not have any alias for the name, so, while you will try to execute this code, you will get:

Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

My guess is that is why only ~50% percent people guessed correctly, the rest might assume that lack of an alias is the "tricky" part of this question...


+1.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search