Comment réaliser un Upsert dans SQL Server : Combinaison des opérations d’insertion et de mise à jour

Lorsque l’on travaille avec des bases de données, nous rencontrons souvent des situations où nous devons gérer des enregistrements qui peuvent ou non déjà exister. C’est ici qu’intervient le concept d’Upsert, nous permettant de combiner de manière transparente les opérations INSERT et UPDATE. Dans cet article, nous allons explorer un défi courant dans SQL Server et vous fournir une solution qui implémente efficacement la logique Upsert.

Le problème : Gestion des affectations de postes

Imaginez que vous ayez une vue qui répertorie les affectations de postes, montrant qui est assigné à chaque poste et son stade actuel. Votre objectif est de créer une procédure stockée qui retourne un comptage des postes pour chaque membre du personnel à différents stades.

La tentative initiale consiste à insérer des comptages dans une table temporaire. Cependant, un problème clé se pose lorsque vous découvrez que les membres du personnel ayant des postes à plus d’un stade se traduisent par des lignes séparées dans votre table de résultats. Au lieu de cela, vous avez besoin d’une méthode qui mettra à jour une ligne existante pour un membre du personnel ou insérera une nouvelle ligne si aucune n’existe.

Voici un exemple simplifié du code que vous avez commencé à utiliser :

DECLARE @ResultTable table 
(
  StaffName nvarchar(100),
  Stage1Count int,
  Stage2Count int
)

INSERT INTO @ResultTable (StaffName, Stage1Count)
  SELECT StaffName, COUNT(*) FROM ViewJob
  WHERE InStage1 = 1
  GROUP BY StaffName

INSERT INTO @ResultTable (StaffName, Stage2Count)
  SELECT StaffName, COUNT(*) FROM ViewJob
  WHERE InStage2 = 1
  GROUP BY StaffName

Dans cet extrait, un membre du personnel avec des postes dans les deux stades apparaît deux fois dans le @ResultTable, ce qui n’est pas le résultat souhaité.

La solution : Réaliser un Upsert

Pour résoudre ce problème, nous devons mettre en œuvre une approche Upsert qui combine à la fois l’insertion et la mise à jour des enregistrements dans le @ResultTable. Voici une répartition étape par étape de la façon de procéder :

Étape 1 : Initialiser la table de résultats avec tous les membres du personnel

Commencez par insérer tous les membres du personnel dans votre table de résultats avec des comptages initiaux définis à zéro. Cela garantit que chaque membre du personnel est représenté dans la table.

INSERT INTO @ResultTable (StaffName, Stage1Count, Stage2Count)
SELECT StaffName, 0, 0 FROM ViewJob
GROUP BY StaffName

Étape 2 : Mettre à jour les comptages des stades

Ensuite, nous devons mettre à jour les comptages pour chaque stade. Exécutez les commandes SQL suivantes, qui utilisent l’instruction UPDATE pour définir les valeurs en fonction des comptages des postes de votre vue :

Mettre à jour le comptage du Stage 1

UPDATE @ResultTable
SET Stage1Count = (
  SELECT COUNT(*) FROM ViewJob
  WHERE InStage1 = 1 AND @ResultTable.StaffName = StaffName
)

Mettre à jour le comptage du Stage 2

UPDATE @ResultTable
SET Stage2Count = (
  SELECT COUNT(*) FROM ViewJob
  WHERE InStage2 = 1 AND @ResultTable.StaffName = StaffName
)

Conclusion

En suivant ces étapes, vous parvenez à réaliser une opération Upsert efficace dans SQL Server sans avoir besoin de recourir à des curseurs ou à des itérations complexes. Cette méthode vous permet de gérer et de maintenir facilement des comptages précis des postes pour chaque membre du personnel à travers les différents stades de votre base de données.

Dernières réflexions

L’utilisation de la méthodologie Upsert améliore non seulement l’efficacité de vos requêtes SQL, mais simplifie également vos efforts de gestion de base de données. Une représentation claire des affectations du personnel améliorera considérablement les analyses de données et les résultats des rapports.

Si vous avez des questions ou avez besoin d’une assistance supplémentaire avec vos requêtes SQL, n’hésitez pas à nous le faire savoir dans les commentaires ci-dessous !