SET IDENTITY_INSERT (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

Autorise l'insertion de valeurs explicites dans la colonne d'identité d'une table.

Conventions de la syntaxe Transact-SQL

Syntaxe

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

Arguments

database_name
Nom de la base de données qui contient la table spécifiée.

schema_name
Nom du schéma auquel appartient la table.

table_name
Nom de la table comportant une colonne d'identité.

Notes

À n'importe quel moment, seule une table de la session peut avoir la propriété IDENTITY_INSERT activée (ON). Si la propriété d'une table est déjà activée lorsqu'une instruction SET IDENTITY_INSERT ON est émise pour une autre table, SQL Server retourne un message d'erreur signalant que la propriété SET IDENTITY_INSERT est déjà activée, en spécifiant la table correspondante.

Si la valeur insérée est supérieure à la valeur d'identité actuelle de la table, SQL Server utilise automatiquement la nouvelle valeur comme valeur d'identité actuelle.

L'option SET IDENTITY_INSERT est définie lors de l'exécution, et non pas durant l'analyse.

Autorisations

L'utilisateur doit posséder la table ou l'autorisation ALTER sur la table.

Exemples

L'exemple ci-dessous crée une table comportant une colonne d'identité et montre comment l'option SET IDENTITY_INSERT peut être utilisée pour combler un vide dans les valeurs d'identité, résultant d'une instruction 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  

Voir aussi

CREATE TABLE (Transact-SQL)
IDENTITY (Property) (Transact-SQL)
SCOPE_IDENTITY (Transact-SQL)
INSERT (Transact-SQL)
Instructions SET (Transact-SQL)