December 20, 2002 at 12:04 pm
Hi,
When I run INSERT query on one of my table which has Identity field, I have this error
"Server: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'ScheduleDetailCopied' can only be specified when a column list is used and IDENTITY_INSERT is ON."
Then I run
SET IDENTITY_INSERT ScheduleDetailCopied ON
GO
and rerun the INSERT again, but still receiving error. I don't know why? Anyone know about this? Thanks in advance.
Han Nguyen
December 20, 2002 at 12:28 pm
Can you give us the insert statement and explain a little more about what you are doing? And why?
Basically if your insert statement doesn't explicitly define the fields that may be why you are having the trouble.
IE:
-- If you have the two table structures below
CREATE TABLE FOO
(
intID int IDENTITY(1,1),
COL1 varchar(20),
COL2 varchar(20)
)
CREATE TABLE BOO
(
intID int IDENTITY(1,1),
COL1 varchar(20),
COL2 varchar(20)
)
INSERT INTO FOO
SELECT * from BOO -- will fail
VS
INSERT INTO FOO(COL1, COL2)
SELECT COL1, COL2
FROM BOO
Typically you don't want to explicitly have the identity column filled in but have it automatically filled in(The main reason to use them)
Gary Johnson
DBA
Sr. DB Engineer
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.
December 20, 2002 at 12:34 pm
I bet you are not specifying a column list in your INSEERT statement. Instead
INSERT ScheduleDetailCopied
VALUES( ... )
try this:
INSERT ScheduleDetailCopied( ... )
VALUES( ... )
December 20, 2002 at 2:40 pm
Thanks a lot. It was a big help.
Han,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply