Insert Update stored proc sur SQL Server

Question

J'ai écrit une procédure stockée qui effectue une mise à jour si un enregistrement existe, sinon elle effectue une insertion. Cela ressemble à quelque chose comme ceci :

update myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
insert into myTable (Col1, Col2) values (@col1, @col2)

La logique qui me pousse à l'écrire de cette façon est que la mise à jour effectuera une sélection implicite à l'aide de la clause where et que si elle renvoie 0, l'insertion aura lieu.

L'alternative à cette méthode serait d'effectuer une sélection, puis, en fonction du nombre de lignes retournées, de procéder à une mise à jour ou à une insertion. J'ai considéré cette méthode comme inefficace, car si vous effectuez une mise à jour, cela entraînera deux sélections (la première explicite et la seconde implicite dans le cas de la mise à jour). Si la proc devait faire une insertion, il n'y aurait aucune différence d'efficacité.

Ma logique est-elle bonne ? Est-ce ainsi que l'on combine une insertion et une mise à jour dans une procédure stockée ?

Answer 1

Votre hypothèse est juste, c'est la façon optimale de le faire et cela s'appelle upsert/merge .

Importance de l'UPSERT - de sqlservercentral.com :

Pour chaque mise à jour dans le cas mentionné ci-dessus, nous supprimons une lecture supplémentaire de la table. lecture supplémentaire de la table si nous utilisons l'UPSERT au lieu de l'EXISTS. Malheureusement pour une insertion, les méthodes les méthodes UPSERT et IF EXISTS utilisent le même même nombre de lectures sur la table. Par conséquent, la vérification de l'existence ne devrait être effectué que lorsqu'il y a une raison très valable pour justifier les l'E/S supplémentaire. La manière optimisée de de faire les choses est de s'assurer que vous que vous ayez le moins de lectures possible sur la BD.

La meilleure stratégie est de tenter la mise à jour. Si aucune ligne n'est affectée par la mise à jour, alors insérez. Dans la plupart des circonstances, la ligne existera déjà existera déjà et une seule E/S sera nécessaire.

Modifier : Veuillez vérifier cette réponse et l'article de blog lié pour en savoir plus sur les problèmes liés à ce modèle et sur la manière de le rendre sûr.

Answer 2

Veuillez lire le article sur mon blog pour un bon modèle sûr que vous pouvez utiliser. Il y a beaucoup de choses à prendre en compte, et la réponse acceptée à cette question est loin d'être sûre.

Pour une réponse rapide, essayez le modèle suivant. Il fonctionne parfaitement sur SQL 2000 et supérieur. SQL 2005 vous offre une gestion des erreurs qui ouvre d'autres options et SQL 2008 vous offre une commande MERGE.

begin tran
   update t with (serializable)
   set hitCount = hitCount + 1
   where pk = @id
   if @@rowcount = 0
   begin
      insert t (pk, hitCount)
      values (@id,1)
   end
commit tran

Answer 3

S'il doit être utilisé avec SQL Server 2000/2005, le code original doit être inclus dans une transaction pour s'assurer que les données restent cohérentes dans un scénario concurrent.

BEGIN TRANSACTION Upsert
update myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
insert into myTable (Col1, Col2) values (@col1, @col2)
COMMIT TRANSACTION Upsert

Cela entraînera des coûts de performance supplémentaires, mais garantira l'intégrité des données.

De plus, comme cela a déjà été suggéré, MERGE devrait être utilisé lorsqu'il est disponible.

Answer 4

À propos, MERGE est l'une des nouvelles fonctionnalités de SQL Server 2008.

Answer 5

Non seulement vous devez l'exécuter dans une transaction, mais vous avez également besoin d'un niveau d'isolation élevé. En fait, le niveau d'isolation par défaut est Read Commited et ce code a besoin de Serializable.

SET transaction isolation level SERIALIZABLE
BEGIN TRANSACTION Upsert
UPDATE myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
  begin
    INSERT into myTable (ID, Col1, Col2) values (@ID @col1, @col2)
  end
COMMIT TRANSACTION Upsert

L'ajout de la vérification de l'@@erreur et du retour en arrière pourrait être une bonne idée.