January 23, 2004 at 9:32 am
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
insert #Test2 (account, volume)
Select Account
, 1 As Volume
From #Test1
Group By Account
January 23, 2004 at 12:14 pm
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
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