[MSSQL] ID열의 명시적 값은 열 목록이 사용되고 IDENTITY_INSERT가 ON 일 때만 지정할 수 있습니다.

오류내용

 ID열의 명시적 값은 열 목록이 사용되고 IDENTITY_INSERT가 ON 일 때만 지정할 수 있습니다.

 

오류원인

MSSQL에서 해당 테이블의 특정 컬럼에 IDENTITY 속성이 적용되어 있을 경우 임의로 지정한 값을 insert하려고 할 때 발생합니다.

(insert 명령어 실행시 IDENTITY 속성으로 지정된 컬럼은 max+1의 값이 자동으로 생성되어 저장됩니다.)

 

create table test001 (

T_IDX int identity(1,1) not null,

name varchar(20)

)

해결방법

1. IDENTITY_INSERT ON 명령어를 실행하여 명시적으로 입력이 가능하도록 변경

- SET IDENTITY_INSERT 테이블명 ON

 

2.   insert 쿼리 실행

- INSERT INTO 테이블명( num, name) VALUSE(1,'일번')

 

3. IDENTITY_INSERT가 OFF 명령어를 실행하여 명시적으로 입력이 불가능하도록 변경

- SET IDENTITY_INSERT 테이블명 OFF

 

주의사항

IDENTITY_INSERT ON 으로 변경하고 작업 완료 후 꼭 IDENTITY_INSERT가 OFF로 변경해야 운영시에 문제가 발생하지 않습니다.

 

실습쿼리

/*테이블 생성(identity 속성 컬럼 포함)*/

--drop table test001

create table test001 (

T_IDX int identity(1,1) not null,

name varchar(20)

)



--데이터 입력(자동)

insert into test001 values ('일번')



--데이터 조회

select * from test001



--데이터 입력(수동) -> 오류발생

insert into test001(T_IDX,name) values (2,'이번')

select * from test001



-- IDENTITY_INSERT ON으로 설정변경

SET IDENTITY_INSERT test001 ON ;



--데이터 입력(수동) -> 오류없음

insert into test001(T_IDX,name) values (2,'이번')

select * from test001



-- IDENTITY_INSERT OFF으로 설정원복

SET IDENTITY_INSERT test001 OFF ;

 

참고사항(IDENTITY 속성)

1. 현재 값 확인

select current_id = IDENT_CURRENT('테이블명')

 

2. 초기화

DBCC CHECKIDENT('테이블명',RESEED,0) --자동증가 값을 0으로 변경