SET IDENTITY_INSERT (Transact-SQL)

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsMicrosoft Fabric SQL Database

この文を使うことで、テーブルの IDENTITY 列に明示的な値を挿入できます。

この記事とIDENTITY構文は、SQL データベース エンジンのプラットフォームによって異なります。 Microsoft Fabric Data Warehouseについては、バージョンのドロップダウンリストからFabric Data Warehouseを選択してください。

Transact-SQL 構文表記規則

構文

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

引数

database_name

指定されたテーブルが存在するデータベース名。

schema_name

テーブルを含むスキーマの名前。

table_name

ID 列を持つテーブルの名前。

解説

IDENTITY_INSERT プロパティを ONに設定できるのは、セッション内のテーブルが 1 つだけです。 もしテーブルにすでにこのプロパティがONに設定されていて、別のテーブルに対してSET IDENTITY_INSERT ON文を発行すると、SQL ServerはすでにSET IDENTITY_INSERTONとエラーメッセージを返し、ONが設定されているテーブルを報告します。

• IDENTITYの引数が正で、挿入された値がテーブルの現在の識別子値より大きい場合、SQL データベース エンジンは自動的に新たに挿入された値を現在の識別子として使用します。
• IDENTITYの引数が負で、挿入された値がテーブルの現在の恒等元値より小さい場合、SQL Server自動的に新たに挿入された値を現在の恒等元値として使用します。

SET IDENTITY_INSERT の設定は、解析時ではなく実行時に設定されます。

アクセス許可

テーブルの所有者か、テーブル ALTER 許可が必要です。

例

次の例では、ID 列を含むテーブルを作成した後、SET IDENTITY_INSERT ステートメントによって ID 値に発生したギャップを、DELETE の設定を使用して調整しています。

USE AdventureWorks2022;
GO

ツール テーブルを作成します。

CREATE TABLE dbo.Tool
(
    ID INT IDENTITY NOT NULL PRIMARY KEY,
    Name VARCHAR (40) NOT NULL
);
GO

products テーブルに値を挿入します。

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

関連コンテンツ

• CREATE TABLE (Transact-SQL)
• CREATE TABLE (Transact-SQL) IDENTITY (プロパティ)
• SCOPE_IDENTITY(Transact-SQL)
• INSERT (Transact-SQL)
• SET ステートメント (Transact-SQL)

適用対象:Microsoft Fabric のウェアハウス

SET IDENTITY_INSERTを使って、Fabric Data WarehouseのテーブルのIDENTITY列に明示的な値を挿入します。 データ移行、災害復旧、またはセンチネル値を次元テーブルに入力する際など、特定の値をアイデンティティカラムに挿入する必要がある場合に SET IDENTITY_INSERT を活用してください。

Transact-SQL 構文表記規則

構文

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

引数

schema_name

テーブルを含むスキーマの名前。

table_name

ID 列を持つテーブルの名前。

解説

IDENTITY_INSERT プロパティを ONに設定できるのは、セッション内のテーブルが 1 つだけです。 あるテーブルですでにこのプロパティが ON に設定されていて、別のテーブルに対して SET IDENTITY_INSERT ON を発行した場合、エラーがそのプロパティがすでに設定されているテーブルを特定します。

明示的な挿入を完了した後、IDENTITY_INSERTをOFFに戻し、RESEEDでDBCC CHECKIDENTを実行して識別子の範囲を再調整し、将来の自動生成値との競合を防ぎます。

Fabric Data Warehouse、IDENTITY_INSERTが使われた際に単位の値の一意性を保証するわけではありません。 明示的に挿入された値は重複を生む可能性があります。システムが新たな値を生成する前に識別子のメタデータを再調整する DBCC CHECKIDENT を実行しない限り。

アクセス許可

テーブルの所有者か、テーブル ALTER 許可が必要です。

制限事項

SET IDENTITY_INSERT INSERT文とCOPY 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'
);

関連コンテンツ

• IDENTITYFabric Data Warehouseの柱
• DBCC CHECKIDENT(Transact-SQL)
• チュートリアル:Fabric Data WarehouseでIDENTITY列を使う
• IDENTITY列をFabric Data Warehouseに移行する