IDENTITY가 설정된 컬럼에 데이터 입력하기-SET IDENTITY_INSERT

MS SQL Server에서 일련번호처럼 일정한 증가 값을 가진 데이터를 입력하기 하기 위해서는 어떻게 해야 할까?

몇 가지 방법이 있겠지만, 일반적으로 테이블의 열에 IDENTITY 속성을 지정하는 방법을 많이 사용한다.

속성이라는 말에서도 알 수 있듯이 Oracle의 Sequence 같은 객체는 아니다. 

오라클에서는 미리 작성해 둔 Sequence 객체에서 NEXTVAL을 이용해서 다음 값을 취득하여 사용하지만, IDENTITY 속성은 CREATE TABLE 및 ALTER TABLE Transact-SQL 문을 이용하여 컬럼에 지정하게 된다 (이하 IDENTITY에 대한 더 자세한 내용은 여기(BOL)를 참고하길 바란다).

 

이 때, IDENTITY 속성이 지정된 열에 테이터를 입력하고자 할 때 다음과 같은 오류 메시지를 접하게 될 것이다.

 

* IDENTITY_INSERT가 OFF로 설정되면 'XXX' 테이블의 ID 열에 명시적 값을 삽입할 수 없습니다.

 

그렇다면 IDENTITY 속성이 지정된 열에 임시적으로 데이터를 입력하려면 어떻게 해야 할까?

☞ SET IDENTITY_INSERT 문을 사용해야 한다.

 

예를 통해 그 사용법을 간단하게 살펴보도록 하자.

 

먼저 dbo.identity_insert_test 이라는 이름으로 예제 테이블을 작성하도록 하겠다. 이 테이블의 seq 열에 1부터 1씩 증가하도록 IDENTITY 속성을 지정하고, 데이터는 10건을 입력하겠다.

IF EXISTS (SELECT *              FROM sys.objects             WHERE object_id = OBJECT_ID(N'[dbo].[identity_insert_test]')               AND type in (N'U')) BEGIN       DROP TABLE dbo.identity_insert_test END   CREATE TABLE dbo.identity_insert_test (seq INT IDENTITY(1,1),  cname VARCHAR(10),  CONSTRAINT identity_insert_test_pk PRIMARY KEY(seq) ) GO   DECLARE @loop_cnt INT; SET @loop_cnt = 1; WHILE @loop_cnt <= 10 BEGIN     INSERT INTO dbo.identity_insert_test(cname) VALUES ( 'test');       SET @loop_cnt = @loop_cnt + 1; END GO   SELECT * FROM dbo.identity_insert_test GO   Result >> seq         cname ----------- ---------- 1           test 2           test 3           test 4           test 5           test 6           test 7           test 8           test 9           test 10          test

 

이제 IDENTITY속성이 지정된 열에 데이터를 입력해 보도록 해보자.

INSERT INTO dbo.identity_insert_test (seq, cname) VALUES (12, 'new') GO

 

그 결과 다음과 같은 오류 메시지와 함께 에러가 발생할 것이다.

메시지 544, 레벨 16, 상태 1, 행 1 IDENTITY_INSERT가 OFF로 설정되면 'identity_insert_test' 테이블의 ID 열에 명시적 값을 삽입할 수 없습니다.

 

여기서 유심히 살펴보면, 친절(?)하게도 오류 메시지가 해당 열에 데이터를 입력할 수 있는 방법을 보여주고 있다.

IDENTITY_INSERT가 OFF로 설정되면 이라고... 즉 IDENTITY_INSERT를 ON으로 설정하면 가능하다는 얘기?

 

속는 셈 치고 IDENTITY_INSERT를 ON으로 설정한 후, 다시 한 번 SQL을 실행해 보도록 하자.

SET IDENTITY_INSERT identity_insert_test ON INSERT INTO dbo.identity_insert_test (seq, cname) VALUES (12, 'new') GO

 

다음과 같이 오류없이 잘 입력되었음을 확인할 수 있을 것이다.

SELECT * FROM identity_insert_test GO   Result >> seq         cname ----------- ---------- 1           test 2           test 3           test 4           test 5           test 6           test 7           test 8           test 9           test 10          test 12          new

 

결과 확인이 끝났으면 IDENTITY_INSERT을 다시 OFF으로 설정하고 IDENTITY 속성이 잘 동작하는지 확인해 보자

SET IDENTITY_INSERT identity_insert_test OFF INSERT INTO dbo.identity_insert_test (cname) VALUES ('new') GO

 

실행 결과는 다음과 같이 나타날 것이다. 다음 행에 seq컬럼의 최대값 + 1 이 입력되었다.

SELECT * FROM dbo.identity_insert_test GO   Result >> seq         cname ----------- ---------- 1           test 2           test 3           test 4           test 5           test 6           test 7           test 8           test 9           test 10          test 12          new 13          new

 

SET IDENTITY_INSERT 구문에 대한 더 자세한 내용은 여기(BOL)을 참고하길 바란다.

 

 

☞ 이 밖의 IDENTITY 관련 팁

1. 현재 IDENTITY값이 열에 저장되어 있는 최대 값보다 작을 경우, 열의 최대값을 사용하여 다시 설정하고자 할 때

   DBCC CHECKIDENT ( table_name ) 또는 DBCC CHECKIDENT ( table_name, RESEED )

    
2. 현재 IDENTITY값을 특정 값으로 초기화 할 경우

   DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )

    
   테이블이 생성된 후 삽입된 행이 없거나, TRUNCATE TABLE문을 사용하여 모든 행을 제거한 경우에는 DBCC CHECKIDENT를 실행한 후에 처음 입력된 행이 new_reseed_value를 값으로 사용하게 된다. 따라서 그 다음에 입력된 행은 new_reseed_value + 증분 값을 사용한다.
   열에 PRIMARY KEY, UNIQUE 제약 조건이 있는 경우 충돌이 발생하지 않도록 주의해야 할 것이다.
    
   ※ DBCC CHECKIDENT 의 BOL