적용 대상:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsMicrosoft Fabric의 SQL 데이터베이스
이 문장을 사용하면 테이블의 열에 IDENTITY 명시적인 값을 삽입할 수 있습니다.
이 글과 IDENTITY 문법은 SQL 데이터베이스 엔진의 다양한 플랫폼에서 다릅니다. Microsoft Fabric Data Warehouse 경우 버전 드롭다운 목록에서 Fabric Data Warehouse을 선택하세요.
구문
SET IDENTITY_INSERT [ [ database_name . ] schema_name . ] table_name { ON | OFF }
인수
database_name
지정된 테이블이 위치한 데이터베이스 이름입니다.
schema_name
테이블을 포함하는 스키마의 이름입니다.
table_name
ID 열이 있는 테이블의 이름입니다.
설명
언제든지 세션의 테이블 하나만 IDENTITY_INSERT 속성을 ON설정할 수 있습니다. 만약 어떤 테이블이 이미 이 속성으로 설정ON되어 있고, 다른 테이블에 대해 문장을 SET IDENTITY_INSERT ON 발행하면, SQL Server는 이미 SET IDENTITY_INSERT가 임을 나타내 ON 는 오류 메시지를 반환하고, 해당 테이블이 설정된 것을 ON 보고합니다.
- 함수의 IDENTITY 인자가 양수이고, 삽입된 값이 테이블의 현재 식별 값보다 클 때, SQL 데이터베이스 엔진은 새로 삽입된 값을 자동으로 현재 식별 값으로 사용합니다.
- 함수의 IDENTITY 인수가 음수이고 삽입된 값이 테이블의 현재 식별 값보다 작을 때, SQL Server는 새로 삽입된 값을 자동으로 현재 식별 값으로 사용합니다.
SET IDENTITY_INSERT 설정은 구문 분석 시간이 아니라 실행 또는 런타임에 설정됩니다.
사용 권한
테이블을 소유하거나 테이블에 대한 허가를 받아야 ALTER 합니다.
예
다음 예에서는 ID 열이 있는 테이블을 만든 다음 SET IDENTITY_INSERT 설정을 사용하여 DELETE 문으로 인해 생긴 ID 값의 차이를 채우는 방법을 보여 줍니다.
USE AdventureWorks2022;
GO
도구 테이블을 만듭니다.
CREATE TABLE dbo.Tool
(
ID INT IDENTITY NOT NULL PRIMARY KEY,
Name VARCHAR (40) NOT NULL
);
GO
제품 테이블에 값을 삽입합니다.
INSERT INTO dbo.Tool (Name)
VALUES ('Screwdriver'),
('Hammer'),
('Saw'),
('Shovel');
GO
ID 값에 간격을 만듭니다.
DELETE dbo.Tool
WHERE Name = 'Saw';
GO
SELECT *
FROM dbo.Tool;
GO
명시적 ID 값 3을 삽입합니다.
INSERT INTO dbo.Tool (ID, Name)
VALUES (3, 'Garden shovel');
GO
앞선 INSERT 코드는 다음과 같은 오류를 반환합니다:
An explicit value for the identity column in table 'AdventureWorks2022.dbo.Tool' can only be specified when a column list is used and IDENTITY_INSERT is ON.
IDENTITY_INSERT
ON설정합니다.
SET IDENTITY_INSERT dbo.Tool ON;
GO
명시적 ID 값 3을 삽입합니다.
INSERT INTO dbo.Tool (ID, Name)
VALUES (3, 'Garden shovel');
GO
SELECT *
FROM dbo.Tool;
GO
도구 테이블을 놓습니다.
DROP TABLE dbo.Tool;
GO
관련 콘텐츠
적용 대상:Microsoft Fabric의 웨어하우스
Fabric Data Warehouse에서 테이블의 열에 IDENTITY 명시적인 값을 삽입하는 데 사용 SET IDENTITY_INSERT 하세요. 데이터 마이그레이션, 재해 복구, 차원 테이블에 센티넬 값을 채울 때 특정 값을 아이덴티티 열에 삽입해야 할 때 사용 SET IDENTITY_INSERT 하세요.
구문
SET IDENTITY_INSERT [ schema_name. ] table_name { ON | OFF }
인수
schema_name
테이블을 포함하는 스키마의 이름입니다.
table_name
ID 열이 있는 테이블의 이름입니다.
설명
언제든지 세션의 테이블 하나만 IDENTITY_INSERT 속성을 ON설정할 수 있습니다. 만약 어떤 테이블이 이미 이 속성이 설정 ON 되어 있고 다른 테이블에 대해 발행 SET IDENTITY_INSERT ON 한다면, 해당 속성이 이미 설정된 테이블에 오류가 발생한다.
명시적 삽입을 완료한 후에는 다시 로 설정 IDENTITY_INSERTOFF 하여 DBCC CHECKIDENT 를 실행 RESEED 하여 식별자 범위를 재정렬하고 앞으로 자동 생성된 값과의 잠재적 충돌을 방지합니다.
Fabric Data Warehouse 사용될 때 IDENTITY_INSERT 동일성 값의 유일성을 보장하지는 않습니다. 명시적으로 삽입된 값은 중복 데이터를 유발할 수 있으며, 시스템이 추가 값을 생성하기 전에 식별 메타데이터를 재정렬하지 않는 DBCC CHECKIDENT 한 발생할 수 있습니다.
사용 권한
테이블을 소유하거나 테이블에 대한 허가를 받아야 ALTER 합니다.
제한점
SET IDENTITY_INSERT 그리고 문장에만 INSERTCOPY INTO 적용됩니다. 기존 아이덴티티 컬럼 값을 업데이트할 수 없어요.
예
A. 센티넬 값을 차원 테이블에 삽입하세요
가장 일반적인 용도 IDENTITY_INSERT 는 데이터 웨어하우스 설정이나 마이그레이션 시 차원 테이블에 "알 수 없음"과 같은 -1 센티넬 값을 채우는 것입니다.
-- Create a dimension table with an IDENTITY column
CREATE TABLE dbo.DimCustomer (
CustomerKey BIGINT IDENTITY,
CustomerName VARCHAR(100),
Email VARCHAR(200)
);
-- Enable IDENTITY_INSERT to add sentinel rows
SET IDENTITY_INSERT dbo.DimCustomer ON;
INSERT INTO dbo.DimCustomer (CustomerKey, CustomerName, Email)
VALUES (-1, 'Unknown', 'N/A');
INSERT INTO dbo.DimCustomer (CustomerKey, CustomerName, Email)
VALUES (-2, 'Not Applicable', 'N/A');
SET IDENTITY_INSERT dbo.DimCustomer OFF;
-- Reseed to prevent conflicts with future auto-generated values
DBCC CHECKIDENT('dbo.DimCustomer', RESEED);
B. 기존 신원 값을 유지하면서 데이터를 마이그레이션합니다
SQL Server나 Azure Synapse Analytics에서 마이그레이션할 때는 IDENTITY_INSERT 기존 아이덴티티 값을 유지하고 참조 무결성을 유지하세요.
-- Assume dbo.DimProduct has an IDENTITY column named ProductKey
SET IDENTITY_INSERT dbo.DimProduct ON;
INSERT INTO dbo.DimProduct (ProductKey, ProductName, Category, ListPrice)
VALUES (1, 'Widget A', 'Hardware', 19.99),
(2, 'Widget B', 'Hardware', 29.99),
(3, 'Gadget C', 'Electronics', 49.99);
SET IDENTITY_INSERT dbo.DimProduct OFF;
-- Reseed after migration
DBCC CHECKIDENT('dbo.DimProduct', RESEED);
C. 정체성 값의 공백을 채우기
테이블에서 행이 삭제될 경우, 필요 시 항등성 순서의 빈틈을 채우기 위해 사용 IDENTITY_INSERT 하세요.
CREATE TABLE dbo.Tool (
ID BIGINT IDENTITY,
Name VARCHAR(40) NOT NULL
);
INSERT INTO dbo.Tool (Name)
VALUES ('Screwdriver'), ('Hammer'), ('Saw'), ('Shovel');
-- Delete a row, creating a gap
DELETE FROM dbo.Tool WHERE Name = 'Saw';
-- Fill the gap with an explicit value
SET IDENTITY_INSERT dbo.Tool ON;
INSERT INTO dbo.Tool (ID, Name)
VALUES (3, 'Garden shovel');
SET IDENTITY_INSERT dbo.Tool OFF;
DBCC CHECKIDENT('dbo.Tool', RESEED);
D. COPY INTO 사용으로 명시적 값을 삽입하세요
이 문장은 COPY INTO 명령어 내에서 명시적인 값을 인징하는 옵션을 지원합니다 IDENTITY_INSERT .
COPY INTO 옵션들은 에 대한 세션 레벨 설정을 IDENTITY_INSERT덮어씁니다.
COPY INTO dbo.Employees (EmployeeID 1, FirstName 2, LastName 3)
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/'
WITH (
FILE_TYPE = 'CSV',
IDENTITY_INSERT = 'ON'
);