SET IDENTITY_INSERT (Transact-SQL)

S’applique à :yesSQL Server (toutes les versions prises en charge) YesAzure SQL Database YesAzure SQL Managed Instance yesAzure Synapse Analytics

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

Topic link iconConventions de la syntaxe Transact-SQL

Syntaxe

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

Notes

Pour afficher la syntaxe Transact-SQL pour SQL Server 2014 et versions antérieures, consultez Versions antérieures de la documentation.

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