[MSSQL] IDENTITY_INSERT가 OFF로 설정되면 테이블 열에 명시적 값을 삽입할 수 없습니다

테이블에 데이터를 INSERT 시 아래와 같은 오류가 발생하며 데이터 삽입이 불가능한 경우가 있습니다.

테이블 생성시 IDENTITY 옵션으로 테이블 컬럼이 생성된 경우 인데요.

IDENTITY_INSERT 구문을 사용하여 데이터를 입력하는 방법에 대해서 알아보겠습니다.

---

IDENTITY_INSERT 란

데이터 입력시 명시적 값을(순번) 테이블의 ID 열에 삽입할 수 있도록 합니다.

테이블 생성시 IDENTITY 옵션으로 지정이 가능합니다.

 

[문법]

SET IDENTITY_INSERT [ [ database_name . ] schema_name . ] table_name { ON | OFF } 

 

[예제]

다음 예에서는 ID 열이 있는 테이블을 만든 다음 SET IDENTITY_INSERT 설정을 사용하여

DELETE 문으로 인해 생긴 ID 값의 차이를 채우는 방법을 보여 줍니다.

*사용자는 테이블을 소유하거나 테이블에 대한 ALTER 권한이 있어야 합니다.

 

 

-- 테이블생성  
CREATE TABLE dbo.ET_TABLE(  
   ID INT IDENTITY NOT NULL PRIMARY KEY,   
   Name VARCHAR(40) NOT NULL  
);  
GO  
-- 테이터 입력  
INSERT INTO dbo.ET_TABLE(Name)   
VALUES ('리나')  
        , ('카라')  
        , ('세라')  
        , ('루나');  
GO  
  
-- 중간 데이터 삭제
DELETE dbo.ET_TABLE  
WHERE Name = '세라';  
GO  
    
-- 데이터 확인 
SELECT *   
FROM dbo.ET_TABLE;  
GO

 

삭제된 3번 데이터 '세라' 삽입시도 -> 에러발생

-- 삭제된 3번 데이터 '세라' 삽입시도 -> 에러발생
INSERT INTO dbo.ET_TABLE (ID, Name) VALUES (3, '신규');

IDENTITY_INSERT ON 옵션으로 데이터 입력 허용

-- IDENTITY_INSERT ON 옵션으로 데이터 입력 허용
SET IDENTITY_INSERT dbo.ET_TABLE ON;  --SET IDENTITY_INSERT dbo.ET_TABLE OFF;  
GO  
  
-- 삭제된 3번 데이터 '세라' 삽입시도 -> 삽입성공  
INSERT INTO dbo.ET_TABLE (ID, Name) VALUES (3, '신규');  
GO  

-- IDENTITY_INSERT OFF 옵션으로 데이터 입력 막음
SET IDENTITY_INSERT dbo.ET_TABLE OFF;
GO  

-- 데이터 확인  
SELECT *   
FROM dbo.ET_TABLE;
GO

 

테이블 삭제

-- 테이블 삭제
DROP TABLE dbo.ET_TABLE;  
GO