SET IDENTITY_INSERT (Transact-SQL)

適用於:SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse Analytics

允許將明確的值插入資料表的識別欄位中。

Transact-SQL 語法慣例

Syntax

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

注意

若要檢視 SQL Server 2014 (12.x) 和舊版的 Transact-SQL 語法,請參閱 舊版檔

引數

database_name
這是指定的資料表所在的資料庫名稱。

schema_name
這是資料表所屬的結構描述名稱。

table_name
這是含識別欄位之資料表的名稱。

備註

不論何時,工作階段中只能有一份資料表將 IDENTITY_INSERT 屬性設為 ON。 如果資料表已將此屬性設為 ON,又對另一份資料表發出 SET IDENTITY_INSERT ON 陳述式,SQL Server 就會傳回錯誤訊息,指出 SET IDENTITY_INSERT 已設為 ON,並且報告設為 ON 的資料表。

如果輸入的值大於資料表目前的識別值,SQL Server 會自動使用新插入的值作為目前的識別值。

SET IDENTITY_INSERT 的設定是在執行階段進行設定,而不是在剖析階段進行設定。

權限

使用者必須擁有資料表,或對資料表擁有 ALTER 權限。

範例

下列範例會建立含識別欄位的資料表,且會顯示如何利用 SET IDENTITY_INSERT 設定來填滿 DELETE 陳述式所造成的識別值的間距。

USE AdventureWorks2022;  
GO  
-- Create tool table.  
CREATE TABLE dbo.Tool(  
   ID INT IDENTITY NOT NULL PRIMARY KEY,   
   Name VARCHAR(40) NOT NULL  
);  
GO  
-- Inserting values into products table.  
INSERT INTO dbo.Tool(Name)   
VALUES ('Screwdriver')  
        , ('Hammer')  
        , ('Saw')  
        , ('Shovel');  
GO  
  
-- Create a gap in the identity values.  
DELETE dbo.Tool  
WHERE Name = 'Saw';  
GO  
  
SELECT *   
FROM dbo.Tool;  
GO  
  
-- Try to insert an explicit ID value of 3;  
-- should return an error:
-- 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.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel');  
GO  
-- SET IDENTITY_INSERT to ON.  
SET IDENTITY_INSERT dbo.Tool ON;  
GO  
  
-- Try to insert an explicit ID value of 3.  
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel');  
GO  
  
SELECT *   
FROM dbo.Tool;  
GO  
-- Drop products table.  
DROP TABLE dbo.Tool;  
GO  

另請參閱

CREATE TABLE (Transact-SQL)
IDENTITY (屬性) (Transact-SQL)
SCOPE_IDENTITY (Transact-SQL)
INSERT (Transact-SQL)
SET 陳述式 (Transact-SQL)