SET IDENTITY_INSERT (Transact-SQL)

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics

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

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 的資料表。

如果輸入的值大於資料表目前的識別值，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 tool table.  
DROP TABLE dbo.Tool;  
GO  

另請參閱

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