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

반응형

 

위와 같이 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 제약을 깨고 진행하는 작업인만큼

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