IDENTITY_INSERT is ON still showing an error

Question

i am using following code but not working at all..

SET IDENTITY_INSERT MBR_INC_DTL_ ON
INSERT INTO MBR_INC_DTL_
SELECT * FROM MBR_INC_DTL__

error message showing..

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'MBR_INC_DTL_' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Frederik Gheysels · Answer 1 · 2013-07-29T05:34:51.003

3

Simple, You don't use a column list in your insert statement:

insert into tablename (column1, column2, ...)
select ... From ...

edited Jul 29 '13 at 05:34

answered Jul 29 '13 at 05:33

Frederik Gheysels

53,692
9
95
146

i don't know how to thank you... from yesterday i was on trouble . thanks a lot.. – Nisar Jul 29 '13 at 05:42

score 3 · Accepted Answer · answered Jul 29 '13 at 05:36

3

Identity column must be specified first.

SET IDENTITY_INSERT MBR_INC_DTL_ ON

insert into MBR_INC_DTL_
(identity_column_name,
column2,
..
)
select 
identity_column_name,
column2,
..

SET IDENTITY_INSERT MBR_INC_DTL_ OFF

answered Jul 29 '13 at 05:36

Prahalad Gaggar

10,486
16
46
66

From yesterday i was on trouble . thanks a lot.. for solving my prob. – Nisar Jul 29 '13 at 05:42
2

@NISAR I am glad it helped you. Try accepting it as your answer if it helped you. – Prahalad Gaggar Jul 29 '13 at 05:43

IDENTITY_INSERT is ON still showing an error

2 Answers2