Partager via

SET IDENTITY_INSERT (Transact-SQL)

  • Article

S’applique à :SQL Serverbase de données Azure SQLAzure 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 dans laquelle réside la table spécifiée.

schema_name

Nom du schéma auquel appartient la table.

table_name

Nom d’une table avec une colonne d’identité.

Notes

À tout moment, une seule table d’une session peut avoir la propriété IDENTITY_INSERT définie sur ON. Si une table a déjà cette propriété définie sur ONet qu’une instruction SET IDENTITY_INSERT ON est émise pour une autre table, SQL Server retourne un message d’erreur indiquant que SET IDENTITY_INSERT est déjà ON, et signale la table pour laquelle ON est définie.

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.

Le paramètre de SET IDENTITY_INSERT est défini au moment de l’exécution ou de l’exécution et non au moment de l’analyse.

Autorisations

L’utilisateur doit posséder la table ou disposer d’une 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

Créer une table d’outils.

CREATE TABLE dbo.Tool
(
    ID INT IDENTITY NOT NULL PRIMARY KEY,
    Name VARCHAR (40) NOT NULL
);
GO

Insérez des valeurs dans la table des produits.

INSERT INTO dbo.Tool (Name)
VALUES ('Screwdriver'),
    ('Hammer'),
    ('Saw'),
    ('Shovel');
GO

Créez un écart dans les valeurs d’identité.

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

SELECT *
FROM dbo.Tool;
GO

Essayez d’insérer une valeur d’ID explicite de 3.

INSERT INTO dbo.Tool (ID, Name)
VALUES (3, 'Garden shovel');
GO

Le code INSERT précédent doit retourner l’erreur suivante :

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.

Définissez IDENTITY_INSERT sur ON.

SET IDENTITY_INSERT dbo.Tool ON;
GO

Essayez d’insérer une valeur d’ID explicite de 3.

INSERT INTO dbo.Tool (ID, Name)
VALUES (3, 'Garden shovel');
GO

SELECT *
FROM dbo.Tool;
GO

Supprimer la table d’outils.

DROP TABLE dbo.Tool;
GO

Contenu connexe