Requête Insert or Update SQL Server

Comment écrire une requête SQL Server Insert or Update, c’est à d’insertion ou de mise à jour ? Voici deux solutions différentes, rapides et faciles pour écrire une requête de type « UPDATE OR INSERT ».

En d’autres termes, pour éviter l’erreur SQL Server « Cannot insert duplicate key in object », « The duplicate key value is » car la ligne en question existe déjà dans la table cible.

Exemple de requête Insert or Update avec SQL Server pour éviter la violation de la clé unique

En effet, l’insertion de la même clé provoquera une erreur SQL Server.

Avant de commencer, pour tester les deux solutions de manière pratique, exécuter ce script pour créer une table d’exemple et insérer deux lignes de données. La solution est un simple mélange d’une commande de mise à jour et d’une commande d’insertion.

Dans cet exemple, le code de création de la table SALES est utilisé pour l’exemple INSERT ou UPDATE. Et donc la mise à jour des lignes de la table cible.

Note : ce SQL insert ou update insère des données dans une table très simple, il suffit d’adapter le code au projet actuel.

Après deux insertions consécutives pour le même mois, nous avons obtenu le message d’erreur ci-dessous.

-- Script pour supprimer la table si elle existe déjà 
IF EXISTS (
	SELECT 	1 
	FROM sys.objects
	WHERE 	object_id = object_id(N'[dbo].[SALES]') 
		AND type in (N'U')
)
BEGIN 
	DROP TABLE [dbo].[SALES]
END
GO

-- Créer la table d'exemple avec la colonne mois , MONTH déclarée comme UNIQUE
CREATE TABLE [dbo].[SALES]
(
	[MONTH] 	NVARCHAR(20) UNIQUE,
	[AMOUNT] 	NUMERIC(5)
)
GO

-- Insérer des données sur le mois de Janvier pour l'exemple
INSERT INTO dbo.SALES (MONTH, AMOUNT) VALUES ( N'January', 1000);
INSERT INTO dbo.SALES (MONTH, AMOUNT) VALUES ( N'January', 2000);

Le message d’erreur SQL Server apparaît et ressemble à ceci, avec une version anglaise de SQL Server:

Msg 2627, Level 14, State 1, Line 18
Violation of UNIQUE KEY constraint ‘UQ__SALES__03EA00460E2F3BD5’. Cannot insert duplicate key in object ‘dbo.SALES’. The duplicate key value is (January).
The statement has been terminated.

Les deux approches de SQL Server sont « INSERT OR UPDATE » et « UPDATE OR INSERT »

Il existe en effet deux façons de faire, soit un INSERT OR UPDATE, soit l’inverse, c’est é dire un UPDATE OR INSERT.

La première solution est INSERT OR UPDATE nécessite deux étapes

Tout d’abord, on teste si la ligne à insérer existe dans la table, en utilisant la fonction EXISTS.

Ensuite, en fonction du résultat, si la ligne existe alors nous effectuons un UPDATE pour mettre à jour la valeur, et si elle n’existe pas alors nous lançons un INSERT pour insérer une nouvelle ligne.

En pratique, c’est effectivement un INSERT OR UPDATE, même si l’insertion de données n’est pas exécutée à chaque fois. C’est à dire dans le cas ou la ligne est déjà présente dans la table SQL Server.

IF NOT EXISTS(SELECT * FROM dbo.SALES WHERE MONTH = 'January')
BEGIN
	INSERT INTO dbo.SALES (MONTH, AMOUNT) 
	VALUES ( N'January', 2000);
END
ELSE
	BEGIN
		UPDATE dbo.SALES
		SET AMOUNT = 2000
		WHERE MONTH = 'January';
	END
GO

La deuxième solution est d’effectuer un UPDATE puis vérifier le nombre de lignes mises à jour

Deuxièmement, commencer par une mise à jour de la ligne.

Ensuite, seulement si le nombre de lignes mises à jour est égal à zéro alors exécuter l’instruction INSERT.

Enfin, cette dernière insère une nouvelle ligne pour le mois de janvier, qui est la clé.

UPDATE dbo.SALES
	SET AMOUNT = 2000
	WHERE MONTH = 'January';
	
IF @@ROWCOUNT = 0
BEGIN
	INSERT INTO dbo.SALES ( MONTH, AMOUNT ) 
	VALUES ( N'January', 2000);
END;

Enfin, cet article présente deux façons de vérifier l’existence de données avant d’insérer ou après une mise à jour d’une table SQL Server.

Pour aller plus loin, consulter cet exemple pour insérer des lignes à partir d’une requête select SQL Server.

INSERT INTO avec un SELECT dans SQL Server