위와 같이 MSSQL에서 INSERT 쿼리 실행 시 "테이블 table에 있는 ID 열의 명시적 값은 열 목록이 사용되고 IDENTITY_INSERT가 ON일 때만 지정할 수 있습니다." 라는 오류가 발생했을 경우 조치방법입니다.


먼저 위의 오류가 발생한 원인은 

MSSQL에서 해당 table의 특정 컬럼이 IDENTITY 속성이 적용되어 있을 때

임의로 지정된 값을 insert하려 할 때 "쿼리가 완료되었으나 오류가 발생했습니다." 라는 결과와 함께 위의 에러가 발생합니다.


IDENTITY는 MySQL의 auto_increment와 동일하게 생각하면됩니다.

Insert 시 IDENTITY로 지정된 컬럼은 max + 1의 값이 자동으로 생성되어 저장됩니다.


자동으로 생성되어 저장되지만 직접 특정값을 insert 해야할 상황이 있습니다.

그럴 경우 IDENTITY_INSERT 속성을 ON/OFF 한 후 insert 쿼리를 실행해야 오류가 발생하지 않습니다.


TEST_TABLE(IDX, COL1, COL2)를 예로 들었을 때 TEST_TABLE의 IDX 컬럼에 IDENTITY가 적용이 되어 있다면 1이라는 값을 명시적으로 입력하려 할 때 위의 오류가 발생합니다.



아래는 IDENTITY_INSERT ON/OFF 명령을 적용하여 INSERT 쿼리를 실행하는 쿼리문입니다.


SET IDENTITY_INSERT TEST_TABLE ON;
INSERT INTO TEST_TABLE(IDX, COL1, COL2) VALUES(1, 1, 2);
SET IDENTITY_INSERT TEST_TABLE OFF;


SET IDENTITY_INSERT TEST_TABLE ON; 쿼리는

TEST_TABLE의 IDENTITY 속성이 적용된 column에 직접 명시한 값을 INSERT 하겠다는 명령입니다.


SET IDENTITY_INSERT TEST_TABLE OFF; 쿼리는 위와 반대로 TEST_TABLE의 IDENTITY 속성이 적용된 column을 정상적으로 사용하겠다는 명령입니다. ON을 사용하여 INSERT문을 실행한 후 꼭 OFF로 원상복구 해야 운영에 문제가 없을 것입니다.



위의 쿼리를 실행했을 경우 성공적으로 아래의 결과 메시지를 확인할 수 있습니다.




INSERT 시 기존 Table 제약을 깨고 진행하는 작업인만큼

이 작업을 진행하기 전 테이블 제약조건 등을 충분히 고려한 후 신중하게 작업을 진행하시기 바랍니다.


Posted by 준콩ol

댓글을 달아 주세요

  1. 2016.10.11 16:38  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  2. 이브 2018.06.03 22:37  댓글주소  수정/삭제  댓글쓰기

    안녕하세요 요즘 홈페이지 디자인이나 플러그인관심많아서 티스토리를 찾다가 이렇게 댓글을 다네요 저도 블로그를 운영하면서 공부와 제가아는 상식을 공유 하고싶은데 혹시 초대장 보내주실수있으신가요?

    이메일은 jmjm1208@naver.com 입니다 블로그 개설하면 댓글 달겠습니다

  3. 2018.10.10 18:56  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다