SET IDENTITY_INSERT （Transact-SQL）

適用於：SQL ServerAzure SQL 資料庫Azure SQL 受控執行個體Azure Synapse AnalyticsMicrosoft Fabric 中的 SQL 資料庫

透過此陳述，你可以將明確的值插入資料表的欄位。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

具有標識列的數據表名稱。

備註

在任何時間，工作階段中只有一個資料表可以將 IDENTITY_INSERT 屬性設定為 ON。 如果一個資料表已經將此屬性設為 ON，而你為另一個資料表發出SET IDENTITY_INSERT ON陳述，SQL Server 會回傳一個錯誤訊息，顯示SET IDENTITY_INSERT已經 ON，並回報該資料表所設定的。ON

  • 當increment函式的參數IDENTITY為正，且插入的值大於資料表目前的身份值時，SQL 資料庫引擎 會自動使用新插入的值作為當前身份值。
  • 當increment函式的參數IDENTITY為負，且插入的值小於資料表目前的身份值時，SQL Server 會自動使用新插入的值作為當前身份值。

SET IDENTITY_INSERT 的設定是在運行時間設定，而不是在剖析時間設定。

權限

你必須擁有該桌或擁有 ALTER 桌上許可。

範例

下列範例會建立含識別欄位的資料表，且會顯示如何利用 SET IDENTITY_INSERT 設定來填滿 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

在識別值中建立間距。

DELETE dbo.Tool
WHERE Name = 'Saw';
GO

SELECT *
FROM dbo.Tool;
GO

嘗試插入 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

嘗試插入 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中，請將SET IDENTITY_INSERT明確的值插入IDENTITY資料表的欄位。 當你需要在身份欄位中插入特定值時使用 SET IDENTITY_INSERT ，例如資料遷移、災難復原，或在維度資料表中填充哨兵值時。

Transact-SQL 語法慣例

語法

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

引數

schema_name

包含該資料表的結構名稱。

table_name

具有標識列的數據表名稱。

備註

在任何時間，工作階段中只有一個資料表可以將 IDENTITY_INSERT 屬性設定為 ON。 如果一個資料表已經設定 ON 了這個屬性，而你又為另一個資料表發出 SET IDENTITY_INSERT ON 錯誤，則錯誤會識別出該屬性已經設定的那個資料表。

完成明確插入後，回到 IDENTITY_INSERTOFF 並執行 DBCC CHECKIDENT 以 RESEED 重新對齊身份範圍，避免與未來自動產生的值發生衝突。

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'
);

相關內容

  • Last updated on