관리 메뉴

Data Modeling Evangelist Kaien Kim's Blog

SET IDENTITY_INSERT 본문

DATA/SQLServer

SET IDENTITY_INSERT

2008. 7. 9. 02:13

SET IDENTITY_INSERT


명시적 값을 테이블의 ID 열에 삽입할 수 있도록 합니다.

구문
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

인수
database

지정한 테이블이 있는 데이터베이스의 이름입니다.

owner

테이블 소유자의 이름입니다.

table

ID 열이 있는 테이블의 이름입니다.

비고
언제든지 세션에서 한 테이블의 IDENTITY_INSERT 속성만 ON으로 설정할 수 있습니다. 한 테이블에 이 속성이 ON으로 설정되어 있는데 다른 테이블에 대해 SET IDENTITY_INSERT ON 문을 실행하면 Microsoft? SQL Server™는 SET IDENTITY_INSERT가 이미 ON으로 설정되어 있음을 알리고 ON으로 설정된 테이블을 보고하는 오류 메시지를 반환합니다.

테이블의 현재 ID 값보다 큰 값을 삽입하면 SQL Server는 자동으로 새로 삽입한 값을 현재 ID 값으로 사용합니다.

SET IDENTITY_INSERT 옵션은 실행시간이나 런타임에 설정되며, 구문 분석 시간에는 설정되지 않습니다.

사용 권한
기본적으로 sysadmin 고정 서버 역할, db_owner 및 db_ddladmin 고정 데이터베이스 역할 및 개체 소유자에게 실행 권한이 부여됩니다.

예제
다음 예제는 ID 열이 있는 테이블을 만든 다음 SET IDENTITY_INSERT 설정을 사용해 DELETE 문으로 인해 생긴 ID 값의 차이를 메우는 방법을 보여 줍니다.

-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES ('screwdriver')
INSERT INTO products (product) VALUES ('hammer')
INSERT INTO products (product) VALUES ('saw')
INSERT INTO products (product) VALUES ('shovel')
GO

-- Create a gap in the identity values.
DELETE products
WHERE product = 'saw'
GO

SELECT *
FROM products
GO

-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO

-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO-- SET IDENTITY_INSERT to OFF.
SET IDENTITY_INSERT products OFF
GO

SELECT *
FROM products
GO
-- Drop products table.
DROP TABLE products
GO



#1 기본 사용법

사용예)
<?xml:namespace prefix = p /><?xml:namespace prefix = p />

create table test(
no int identity(1,1) NOT NULL, --(시작할 숫자값, 증가할 숫자값)
name varchar(256) NULL
)

------------------------------------------------------------------------
#2 인서트시 증가된 값 얻어오기

Identity를 설정해준 테이블에 인서트한 경우
인서트한 identity
불러오기 위해서는 보통 다시한번 select를 해주게 된다

인서트 쿼리 뒤에 select @@Identity을 붙여 주면 insert한 row의 identity를 불러올 수 있다.

사용예)
insert into test(name) values('James')
select @@Identity as 'id'  (밑줄은 별칭이므로 아무거라도 상관 없다.)

-------------------------------------------------------------------------
#3 증가값을 임의로 정해보자

:Identity를 선언한 경우에는 인서트 시 ,Identity선언 컬럼의 값임의로 지정할 수 없다
ex) insert into test(no,name) values(1000,'James')
테이블 생성 시 디폴트 값으로 임의의 번호로 지정하고 싶거나,
삭제된 번호를 채워 넣고 싶을 때 유용한 방법이다.

-
SET IDENTITY_INSERT [테이블명] ON;
  증가값을 수동 지정

-SET IDENTITY_INSERT [테이블명] OFF;
 증가값을 자동 지정 (보통 우리가 사용하는 상태 )

-DBCC CHECKIDENT ('[테이블명]', RESEED, 0);
 시작값을 임의 지정

사용예)

SET IDENTITY_INSERT test ON;                      -- Identity 증가값을 수동지정
insert test(no,name) values(99999,'admin')      --수동입력 가능
SET IDENTITY_INSERT product_lank OFF;        --Identity 증가값을 자동지정
                                                                  --※ 증가값이 최초 1-->99999 변경되어짐
                                                                       즉,다음 인서트 값 Identity는 100000 됨

DBCC CHECKIDENT ('product_lank', RESEED, 0);   -- 시작값을 0으로 지정, 다음 증가값은 1이됨.


#4 참고...

- @@IDENTITY, SCOPE_IDENTITY : 현재 세션의 테이블에서 생성된 마지막 ID 값을 반환합니다.
그러나 SCOPE_IDENTITY는 현재 범위 내에서만 값을 반환합니다. @@IDENTITY는 특정 범위로 제한되지 않습니다.

- IDENT_CURRENT :  범위 및 세션으로 제한되지 않고 지정된 테이블로 제한됩니다.
  IDENT_CURRENT는 모든 세션과 범위에 있는 특정 테이블에 생성된 ID 값을 반환합니다.
  예) select IDENT_CURRENT( 'tab3' )

@@IDENTITY 함수의 범위는 이 함수를 실행 중인 로컬 서버의 현재 세션입니다.
이 함수는 원격 서버 또는 연결된 서버에 적용할 수 없습니다.
다른 서버의 ID 값을 가져오려면 해당 원격 서버 또는 연결된 서버에서 저장 프로시저를 실행하고
이 저장 프로시저(원격 서버 또는 연결된 서버의 컨텍스트에서 실행 중)에서 ID 값을 수집하여 로컬 서버의 호출 연결에 반환하도록 합니다.

'DATA > SQLServer' 카테고리의 다른 글

카탈로그내에서 특정 컬럼을 참조하는 SP조회  (0) 2008.11.13
[MSSQL]테이블 소유자 변경하기  (0) 2008.08.20
MSSQL 괜찮은 명령어들  (0) 2008.07.30
Trigger  (0) 2008.07.30
with (nolock) 란?  (0) 2008.07.08