Post reply

Explicit value errors out when using Group By clause

  • January 23, 2004 at 9:32 am

    #106849

    When I insert records into a table that has an IDENTITY column, records with explicit values error out when the table also includes a GROUP BY clause:

    Drop Table #Test1

    Create Table #Test1 (Account varchar(15))

    Insert #Test1 Values ('20320660')

    Insert #Test1 Values ('20321171')

    Insert #Test1 Values ('20321288')

    Drop Table #Test2

    Create Table #Test2 ( IdNum     int IDENTITY

                                 , Account  varchar(15)

                                 , Volume   varchar(3)     )

    -- This works:

    Insert Into #Test2

    Select     Account

                 , (Count(Account)/Count(Account)) As Volume 

    From       #Test1

    Group By Account

    -- This works too:

    Insert Into #Test2

    Select     Account

                 , 1 As Volume 

    From      #Test1

    -- This returns an error message:

    -- Server: Msg 8101, Level 16, State 1, Line 37

    -- An explicit value for the identity column in table '#Test2' can only

    -- be specified when a column list is used and IDENTITY_INSERT is ON.

    Insert Into #Test2

    Select      Account

                  , 1 As Volume 

    From        #Test1

    Group By  Account  

    Can someone help me understand what's going on?  I'm not attempting to insert anything into the identity column, as the error seems to indicate. 

    Thanks

    k2

  • January 23, 2004 at 10:12 am

    #491418

    insert #Test2 (account, volume)

    Select      Account

                  , 1 As Volume

    From        #Test1

    Group By  Account

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 23, 2004 at 12:14 pm

    #491446

    As Steve pointed out this is a case of lazyness catching up with you! This is the best reason to ALWAYS explicitly list out the fields you want to insert into.




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • January 26, 2004 at 10:47 am

    #491658

    Thanks for clearing this up for me - a good learning experience.

    regards

    k2

Viewing 4 posts - 1 through 3 (of 3 total)

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