MSSQL ERROR 8101 테이블 'TABLE_NAME'에 있는 ID 열의 명시적 값은 열 목록이 사용되고 IDENTITY_INSERT가 ON일 때만 지정할 수 있습니다.

컬럼에 identity 속성이 설정되어 있어서 임의적으로 데이터 삽입이 불가능.

 

insert into table_name

select * from table_name;

 

처리시 id값이 증가해야 함으로 컬럼명을 지정하지 않은 상태에서 데이터가 들어갈 수 없음.

 

SET IDENTITY_INSERT table_name ON

insert into table_name

select * from table_name

SET IDENTITY_INSERT table_name OFF

 

로 임시적으로 허용 후 데이터 처리 다시 테이블의 제약조건을 허용하여 처리가 가능함.

 

//** 2024.01.18 내용 추가 **//

위 건으로 처리 되지 않음.

SET IDENTITY_INSERT table_name ON

insert into table_name(column1, column2)

select column1, column2  from table_name

SET IDENTITY_INSERT table_name OFF

 

컬럼을 명시하여 처리해야 오류발생되지 않음.

 

아래는 mssql error내역에 대한 내용과 처리 관련한 내용을 표기함.

http://www.sql-server-helper.com/error-messages/msg-8101.aspx

SQL Server Helper - SQL Server Error Messages

 

SQL Server Error Messages - Msg 8101 Error Message Causes This error happens when you are trying to insert a new record into a table that contains an identity column without specifying the columns in the INSERT statement and you are assigning a value to the identity column instead of letting SQL Server assign the value.

To illustrate on how the error can be encountered:

CREATE TABLE [dbo].[Users] (
    [UserID]    INT NOT NULL IDENTITY(1, 1),
    [UserName]  VARCHAR(20) NOT NULL,
    [FirstName] VARCHAR(50) NOT NULL,
    [LastName]  VARCHAR(50) NOT NULL
)
GO

INSERT INTO [dbo].[Users] 
VALUES ( 1, 'superman', 'Clark', 'Kent' )
GO

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

If you specified the column names in the INSERT statement, you will get a different error message:

INSERT INTO [dbo].[Users] ( [UserID], [UserName], [FirstName], [LastName] )
VALUES ( 1, 'superman', 'Clark', 'Kent' )
GO

Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'Users' when 
IDENTITY_INSERT is set to OFF.

Solution / Workaround:

There are two ways of avoiding any of the errors mentioned above.  The first option is not to include the identity column in the INSERT statement and let SQL Server assign the next identity value to the record:

INSERT INTO [dbo].[Users] ( [UserName], [FirstName], [LastName] )
VALUES ( 'superman', 'Clark', 'Kent' )
GO

The second option is to enable the IDENTITY_INSERT property for the table.  If you really want to specify the value for the identity column, this option is the one for you.

SET IDENTITY_INSERT [dbo].[Users] ON
GO

INSERT INTO [dbo].[Users] ( [UserID], [UserName], [FirstName], [LastName] )
VALUES ( 1, 'superman', 'Clark', 'Kent' )
GO

SET IDENTITY_INSERT [dbo].[Users] OFF
GO

Setting the IDENTITY_INSERT to ON for the table allows explicit values to be inserted into the identity column of a table.  At any given time, only one table in a session can have the IDENTITY_INSERT property set to ON.  If a table already has this property set to ON and a SET IDENTITY_INSERT ON statement is issued on another table, SQL Server will return the following error message:

Msg 8107, Level 16, State 1, Line 2
IDENTITY_INSERT is already ON for table 'dbo.Deparments'. 
Cannot perform SET operation for table 'dbo.Users'.

Execute permissions for the SET IDENTITY_INSERT default to the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles, and the object owner.